[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Current time based on UTC value alone

What is the formula in Google Docs to show the current time based on the UTC value in column A https://docs.google.com/spreadsheets/d/1pG12YlSOs7JuMUt1DosEAdLQI2tmmjhoAWQtsj9xtXY/edit#gid=957433074.
0
frugalmule
Asked:
frugalmule
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
I'm not sure I understand the functionality of this table, but for any given value in column A like "UTC +x" or "UTC -x", the current time for that timezone - where cell A1 contains your computer's timezone (ex., "UTC -5") - would be:
=IF(MID(A7,FIND(" ",A7),4)-MID($A$1,FIND(" ",$A$1),4)>=0,NOW()+TIMEVALUE(MID(A7,FIND(" ",A7),4)-MID($A$1,FIND(" ",$A$1),4)&":00:00"),NOW()-TIMEVALUE(-MID(A7,FIND(" ",A7),4)+MID($A$1,FIND(" ",$A$1),4)&":00:00"))

Still need to apply the DST factor...

Regards,
-Glenn

(Edit: typo in previous formula}
0
 
Ejgil HedegaardCommented:
I think the local UTC and DST value has to be somewhere in the formula, in order to calculate what the time is for another UTC/DST specification, when the input is local time.
Local time to UTC 0 and DST 0, UtcTime = LocalTime +UtcLocal+DstLocal
Then the time for UTC -5, DST -1 is UtcTime-5-1.
With UTC -5 in A2, and DST -1 in B2 the formula is
=TIMEVALUE(TEXT(NOW()+(UtcLocal+DstLocal+VALUE(RIGHT(A2,2))+VALUE(RIGHT(B2,2)))/24,"h:mm"))
UtcLocal and DstLocal could be in cells like the others.
Use DST 0 or DST OFF 0 when no summer time.
0
 
SunBowCommented:
Note that while sheet is changing, it is interested in hours AM/PM (text) and not minutes. I think prior to making all encompassing formula, a simple example should be provided, showing current area of interest, which can be used to both develop and test formula.

This appears to be a one to many problem, or a many to one - possibly experts could answer it both ways to succeed. Or could it be something else. Who is looking at clock to resolve what clock looks like to who? etc., for understanding columns, cells, where main column in current question is multifunctional. Simplification of formula may be able to use row(), for example, to achieve result. I doubt it is a one to one problem, but then again...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
frugalmuleAuthor Commented:
Realizing that I can always go back to a previous version, the goal is to populate the shaded cells in Column C based on the content of Column A and whatever is placed in Column B.  I have made the sheet editable.  Thanks for all the help.
0
 
Glenn RayExcel VBA DeveloperCommented:
The following modified formula will take into account the DST setting for your own timezone (as noted in cell B2) and the DST setting of any of the other timezones.  You only need to show "DST ON" or "DST OFF" to have the correct hour amount added or subtracted accordingly.  Insert in C12 and copy down:
=IF(MID(A12,FIND(" ",A12),4)-MID($A$2,FIND(" ",$A$2),4)>=0,NOW()+TIMEVALUE(MID(A12,FIND(" ",A12),4)-MID($A$2,FIND(" ",$A$2),4)&":00:00"),NOW()-TIMEVALUE(-MID(A12,FIND(" ",A12),4)+MID($A$2,FIND(" ",$A$2),4)&":00:00"))+((IF(RIGHT($B$2,2)="ON",1,0)-IF(RIGHT($B12,2)="ON",1,0))/24)

I edited your GoogleDocs spreadsheet; let me know if this works.

-Glenn
0
 
frugalmuleAuthor Commented:
Glenn, thank you sir.

ex. Fri Time AM/PM (without seconds) is all that is that is needed in Column C.
0
 
Glenn RayExcel VBA DeveloperCommented:
You can leave the formulas in column C and just change the formats to
dddd" "h":"mm" "am/pm

I've updated your GoogleDoc accordingly.  Hope that helps.
-Glenn
0
 
frugalmuleAuthor Commented:
It's fantastic.  Probably should be three letter day.  I really appreciate it.
0
 
Glenn RayExcel VBA DeveloperCommented:
Easy fix; just change format to:
ddd" "h":"mm" "am/pm

I did update your GD file.  I see that you added data validation to column C; very nice.

-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi,

Have you had a chance to review the updated document and formatting?

If acceptible, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Thanks,
-Glenn
0
 
frugalmuleAuthor Commented:
Yes with the exception of the cell for my time which uses the current system time =now().  If the person looking at it is not in my time zone, then that number will be represented incorrectly.
0
 
Glenn RayExcel VBA DeveloperCommented:
Ah, you didn't say that there was an audience that would be in various timezones!

The fix was easy; just copy the formulas from the section below up to cell C2.  Change the "My Time Zone" value in A2 and all other times change relatively.  I update your GS.

-Glenn
0
 
frugalmuleAuthor Commented:
Thank you sir very much.  I'd like to continue the conversation but see no way to ask a related question.
0
 
frugalmuleAuthor Commented:
Now if I had a way to have each outsourcer tell me what time was best for them in a self-explanatory way, I would have a VERY nice solution to my dilemma.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now