Adding Hours Based on Time Zone

[lpw0806]

Hi All,

I am having some trouble adjusting the hours based on Time Zone. For context, I have a dataset where the date / time in column AC is all in EST. I want to adjust this based on Time Zone.

In column AK I used =TIME(HOUR(AC2),0,0) to pull out the hour + AM / PM. However, I want to adjust column AK based on Time Zone (AJ).

So, for example if something came in at 12PM CST (row 5) -- I would need to subtract 1 hour so column AK reflects the correct Time Zone. Instead of 12PM, I need cell AK5 to say 11AM.

Does that make sense? Any advice / help would be much appreciated!

Thank you!!

 

[Fluff]

How about

=HOUR(AC2+IF(AJ2="CST",-1/24,IF(AJ2="EST",-2/24,IF(AJ2="MST",-3/24,0))))

 

[lpw0806]

Close...but still not right. Now instead of 12:08AM it will just say "12AM" when it needs to be 11:59PM the day prior.

I saw something about the MOD formula? Could that help? What about the way I am formatting my cells?

 

[lpw0806]

I am getting closer with something like this...but instead of subtracting hours its subtracting days....does this help at all?

 

[Fluff]

I don't understand, your formula is just using the hour & ignoring the rest

 

[lpw0806]

I know...that is not the right answer I was just trying to give ideas on what else we could try. Haha

I tried your suggestions and am still getting the same issue. When the time is early in the morning (12AM) -- I am getting an error instead of having the formula subtract hours and change the value to 11PM the prior day

 

[Candyman8019]

I would suggest formatting both columns to show date and time...until you get your formula correct. Then format the adjusted column to time only.

 

[Candyman8019]

Are you able to try the formula on a fresh spreadsheet. I am using the exact formula in my test here and it does go to the previous day where required when changing timezones.