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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You can add the hour as follows:
+AC2+0.04166

.04166 is 1/24th of a day or a calculation of (1/24)
 
Upvote 0
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!
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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