Ted Penner
asked on
Calculate time in each country to reflect noon Central Time
I need to calculate the time that it would be in each country when it is 12 noon my time.
This page shows the current time in my time zone https://time.is/CT
This formula in cell H18 and filled down is intended to give me the time that it is now in each country.
I want to adapt this formula for cell S17 to reflect the time that it would be if it were noon in my time zone.
https://docs.google.com/spreadsheets/d/1E92_BsJRzjozCrBt78nc8BoWotzPq29T3VpW_xZCfNA/edit#gid=0
Assistance is greatly appreciated
This page shows the current time in my time zone https://time.is/CT
This formula in cell H18 and filled down is intended to give me the time that it is now in each country.
=IFERROR(NOW()+($H$12*G89)+5*$H$12+IF(R89=1,$H$12,0),"n/a
I want to adapt this formula for cell S17 to reflect the time that it would be if it were noon in my time zone.
https://docs.google.com/spreadsheets/d/1E92_BsJRzjozCrBt78nc8BoWotzPq29T3VpW_xZCfNA/edit#gid=0
Assistance is greatly appreciated
ASKER
Thanks David,
time.is/CT shows the offset as -5.
What would be the offset for 12 PM?
time.is/CT shows the offset as -5.
What would be the offset for 12 PM?
ASKER
Row 234 represents the -5 offset and the time is definitely off from that as it is now 10:39 pm and not 2:39 pm so something is definitely off with the formula to begin with.
If column R indicates whether that country is on daylight savings time or not, then this formula might work for you:
Paul
=IF(R17="No",TIME(12+(ABS(-6-G17)),MINUTE(H17),SECOND(H17)),TIME(12+(ABS(-5-G17)),MINUTE(H17),SECOND(H17)))
It uses the Time function and finds the difference of the UTC's between the Central Time Zone and country listed in each row.Paul
ASKER
Column R has nothing to do with DST on or off. None of the columns denote that currently.
Flyster and Paul, this formula produces a circular reference error.
Flyster and Paul, this formula produces a circular reference error.
=TIME(12+(ABS(-5-G234)),MINUTE(H234),SECOND(H234))
As long as you don't have this formula in either column G or H, there shouldn't be a problem with circular reference. Is H12 your indicator for Daylight Savings Time? If so, modify the formula to:
=IF(H12=1,TIME(12+(ABS(-5-G17)),MINUTE(H17),SECOND(H17)),TIME(12+(ABS(-6-G17)),MINUTE(H17),SECOND(H17)))
Slight change in the formula. H12 should be an absolute reference. Change it to $H$12.
Another issue I just noticed. Column G is hard coded. That's fine for areas that are not subject to Daylight Savings but will give a one hour error in those that are on Daylight Savings. I saw this in row 234. Washington DC is in the Eastern Time Zone and should have a UTC of -4. You can use this formula for each area that is subject to Daylight Savings:
=IF($H$12=1,-4,-5)
ASKER
Fluster, if that formula is for rows that are subject to DST, how do I take care of the ones that are not?
Also, how would I know which UTC values are subject to DST and which ones are not?
Please explain the formula as much as possible.
Also, how would I know which UTC values are subject to DST and which ones are not?
Please explain the formula as much as possible.
Using VBA may be preferable. You may study my articles and demo on the subject:
Time Zones, Windows, and Microsoft Office - Part 1
Time Zones, Windows, and Microsoft Office - Part 2
Time Zones, Windows, and Microsoft Office - Part 1
Time Zones, Windows, and Microsoft Office - Part 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=SUM(0.5+5/24)
A 24 hour day is 1.00 12M is 1/2 of a day therefore it is .5 the + 5/24 is to add 5 hours
12 PM CDT is 5 PM UTC (1700hrs)
A 24 hour day is 1.00 12M is 1/2 of a day therefore it is .5 the + 5/24 is to add 5 hours
12 PM CDT is 5 PM UTC (1700hrs)
=SUM(0.5+5/24) then you add the UTC offset