Adding Hours Based on Time Zone

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38
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!!


1607021270750.png
 
How about
Excel Formula:
=HOUR(AC2+IF(AJ2="CST",-1/24,IF(AJ2="EST",-2/24,IF(AJ2="MST",-3/24,0))))
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
I am getting closer with something like this...but instead of subtracting hours its subtracting days....does this help at all?

1607034809565.png
 
Upvote 0
I don't understand, your formula is just using the hour & ignoring the rest
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top