Adding Hours Based on Time Zone

[lpw0806]

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

 

[Candyman8019]

You can add the hour as follows:
+AC2+0.04166

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

 

[Candyman8019]

similarly, to subtract just change the operation.

 

[Candyman8019]

You could also do this:
=+AC2-TIME(1,0,0)

 

[lpw0806]

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]

No idea what the time zone differences are, but how about

=TIME(HOUR(AC2),0,0)+IF(AJ2="CST",-1/24,IF(AJ2="EST",-2/24,IF(AJ2="MST",-3/24,0)))

 

[Candyman8019]

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]

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

 

[Candyman8019]

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]

Ugh - that unfortunately did not work for me either...still having the same issue for items where the day needs to be adjusted...