# Adding Hours Based on Time Zone

#### lpw0806

##### New Member
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

##### MrExcel MVP, Moderator
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
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
I am getting closer with something like this...but instead of subtracting hours its subtracting days....does this help at all?

#### Fluff

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

#### lpw0806

##### New Member

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
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
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.

Replies
4
Views
480
Replies
2
Views
135
Replies
2
Views
474
Replies
18
Views
746
Replies
2
Views
255

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

### 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