Global time zone converter formula

[cactusjomak]

I am try to create a formula that converts shift times into different timezomes. I have a group of people who work around the world in different time zones and I have to report our staffing hour by hour.

Now the most common time zones are all 4 NA, GMT, CET, IST, and Singapore.
I need to be able to select GMT and see everyones shift times in GMT or if I want PST I can see them in PST.

What I was doing was made a colum each half hour of the day and filled in rows with the shifts by using an "A" tag so I can calculate the head count. I have to do this for all days of the week.

How do I take shift times and convert to a times zone of my choice?
To complicate things is there a formula to adjust for daylight savings?

 

[Robert Mika]

Hi cactusjomak, and welcome to the forum.
It depends how you data table is being set up.
Are the time zones in a separete columns?
You can use an IF statment to say =IF(A1="GMT",B1+3(or whatver the diffrences beetwen zones are..
(A1 woudl be your time zone selector)

Could you post an example of your data?

 

[cactusjomak]

I have an AMS worksheet with cell: E3
In E3 - I have ET, CT, MT, PT

Then I have a 24 hour formula row broke down into 30 minutes cells until we hit 24 hours
(11:00, 11:30, 12:00, 12:30, 13:00...)
=IF($E$3 = "ET","2:00",IF($E$3 = "CT","1:00", IF($E$3 = "MT", "00:00", IF($E$3="PT","23:00"))))

This switches the the time from of every ones shift from EST to whichever timezone I select. If I want to see who is working in PST I just adjust cell E3 to PT and the time value for everyone s shift switches to PST.

I put all the workers under the column C and to mark there shift times I color the cell YELLOW and put a YELLOW A in the hours they work. (i.e.: Jim works 7am to 5pm EST I have a yellow row from 7-5 with a yellow A marking the cells)

I have 2 of the worksheets: EMEA & APJ -In cell E3 I have GMT & CET then IST & SING

My issue is this how do I account for daylight savings in the USA and around the world?
If I have to do 3 different schedule that is fine, but I want to know how to make this simple?
I have a global team around the world I have to account for and so many different timezones. HELP

 

[cactusjomak]

Any suggestions? I'd appreciate any responses.

 

[shg]

See http://www.tushar-mehta.com/excel/timezone.htm and http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

I use this: http://www.mapmaker.com/Sun%20Clock/Sun%20Clock%207.htm