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.
frugalmuleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.