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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=HOUR(AC2+IF(AJ2="CST",-1/24,IF(AJ2="EST",-2/24,IF(AJ2="MST",-3/24,0))))
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

lpw0806

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

New Member
Joined
Jun 14, 2018
Messages
38
I am getting closer with something like this...but instead of subtracting hours its subtracting days....does this help at all?

1607034809565.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
I don't understand, your formula is just using the hour & ignoring the rest
 

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38

ADVERTISEMENT

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

Board Regular
Joined
Dec 2, 2020
Messages
156
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 2, 2020
Messages
156
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,743
Messages
5,742,936
Members
423,765
Latest member
PaulD1984

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
Top