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.

=IFERROR(NOW()+($H$12*G89)+5*$H$12+IF(R89=1,$H$12,0),"n/a

Open in new window

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

David Johnson, CD

you have the offset from UTC so first you have to calculate what 12PM is un UTC
=SUM(0.5+5/24) then you add the UTC offset

Ted Penner

ASKER

Thanks David,

time.is/CT shows the offset as -5.

What would be the offset for 12 PM?

Ted Penner

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.

Flyster

If column R indicates whether that country is on daylight savings time or not, then this formula might work for you:

=IF(R17="No",TIME(12+(ABS(-6-G17)),MINUTE(H17),SECOND(H17)),TIME(12+(ABS(-5-G17)),MINUTE(H17),SECOND(H17)))

Open in new window

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

Ted Penner

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.

=TIME(12+(ABS(-5-G234)),MINUTE(H234),SECOND(H234))

Open in new window

Flyster

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)))

Open in new window

Flyster

Slight change in the formula. H12 should be an absolute reference. Change it to $H$12.

Flyster

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)

Open in new window

Ted Penner

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.

Gustav Brock

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

ASKER CERTIFIED SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Johnson, CD

=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)