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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
You can add the hour as follows:
+AC2+0.04166

.04166 is 1/24th of a day or a calculation of (1/24)
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
similarly, to subtract just change the operation.
 

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38

ADVERTISEMENT

Hey! Thanks for the feedback. Unfortunately just subtracting the # doesn't work, and the value in column AK stay exactly the same.

Similarly, =+AC2-TIME(1,0,0) doesn't work because the # of hours changes depending on the time zone. Sometimes I need to subtract 1, 2, 3 etc. Also, that result still gives me the date (mm/dd/yyyy). I just want the time value (ex 10AM).

Any idea of how to help there would be awesome. Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,645
Office Version
  1. 365
Platform
  1. Windows
No idea what the time zone differences are, but how about
Excel Formula:
=TIME(HOUR(AC2),0,0)+IF(AJ2="CST",-1/24,IF(AJ2="EST",-2/24,IF(AJ2="MST",-3/24,0)))
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You beat me to it Fluff :)
Here's what I came up with:
=+C4+IF(D4="EST",TIME(1,0,0),IF(D4="MST",TIME(2,0,0),IF(D4="CST",TIME(3,0,0),0)))
And also format the cell to be time only rather than date/time.
 

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38
Thank you both! I am getting much closer...but am still having some issues. What (I think) is happening is that for values in column AC that are early in the morning that time & date needs to adjust to the prior date / night.

For example: Row 139 came in at 12:52AM on 2/3/20. That date & time should change to be 11:52PM on 2/2/20.

Does that make sense? That seems to be the last hurdle here.

Appreciate all of the support!!!

1607030404342.png
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Give this one a try. It seems to work for me. NOTE: You'll need to adjust the hour values for the correct time zone, ie 1 2 3 and notice that I have the formula as '-Time..' to indicate to subtract hours if that's the case.
=C4+IF(D4="EST",-TIME(1,0,0),IF(D4="MST",-TIME(2,0,0),IF(D4="CST",-TIME(3,0,0),0)))
 

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38
Ugh - that unfortunately did not work for me either...still having the same issue for items where the day needs to be adjusted...
1607033487353.png
 

Forum statistics

Threads
1,143,677
Messages
5,720,259
Members
422,273
Latest member
linds75

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