Calculating Corrected Time Based on Time Zone

[Falk781]

Hey everyone, could you please help with the following?

In the attached example, I have some incidents that occurred on a specific date and time. The time/date stamp is in Pacific Time. Is there a formula that we could write that would say if the time zone identifier is "eastern" (in adjacent column), then add 3 hours to the time/date to get the "corrected date and time". If the time zone is central, then add 2 hours, if Hawaii -3, etc....

In my mind, it would be a combination of an "If" statement along with the "time" function but I'm sure someone knows this faster than I can figure it out.

Thanks for your help.

[protonLeah]

Using a helper column (K). Enter correction hours as fractions of a day. Name the cells corresponding to each zone. Use

Please Login or Register  to view this content.

in column D.

[Falk781]

Thanks for responding but I have to be honest, i have absolutely no idea how the fractional part factors into this. I have over 10K rows of data that I'm going to have to calculate. Will this work for that?

[bebo021999]

In D2 like this:

=B2+IF(C2="West",0,IF(C2="Central",2,IF(C2="East",3,IF(C2="Hawaii",-3, IF(.....))))

[protonLeah]

In cell B2, you have a date:
5/26/20 13:00
but this date is actually stored in Excel as a decimal number:
43977.54167
The integer part is the number of days since 1/1/1900 and the decimal part is the fraction of the 24 hour day elapsed since 12:00AM
therefore whole numbers are DAYS, fractions are hours. One hour is 1/24 of a day, etc.

Notice that in your original attachment, the "corrected" values have the wrong date!

If you prefer the IF() formulas the you must correct it:

Please Login or Register  to view this content.

I used named cells in my attached workbook to make the formula simpler, all you had to do was drag it down...

[Falk781]

Leah thanks - I notice that if you run the formula without the fraction as in Bebo's example, it actually ramps up the date, but not the time. Also thanks for the mention on the example date mismatch. I'm assuming that the fraction of a day x/24 is exclusive to dates and that's why the If statement recognizes it? I'm just trying to figure out how it realizes not to update the date, only the associated time?

[AliGW]

A day is 24 hours, so if you add 3 divided by 24 to a time, it gives you a 3-hour increase. If you just added 3, it would be three whole days.

[Falk781]

Thanks for your help everyone - I really appreciate it.

[AliGW]

No problem.