Changing Time zones in multiple cells

[cjenkins08]

I have a list of times that look something like this:
8:30
7:00
6:00
7:00
7:00
8:00
10:30
10:30

Only I have many...many.. more in the list.
These are eastern times and I want to be able to select the entire list and subtract either 1, 2, or 3 hours based on time zone.

If it helps any, when I click on the cell the details show 8:30:00 AM

These are actually PM times that I am bringing over and pasting from word. Excel is auto setting the AM.

So, is there a quick way to convert to PM, then subtract the hours and update the list?

Thanks

[etaf]

can you use other columns ?
is it always showing 12 hours out ?

you could just add 12 hours in a new column
and then add the amounts to change to new time zones in different columns

[cjenkins08]

Thanks for the fast reply!
Yes, I can use other columns.
Yes they are all showing AM and I want to convert to PM.

I'm not sure how to add the hours/amounts to multiple cells at one time.
Any helps is appreciated.
Thanks

[NBVC]

You can enter 0.5 (for 12 hours) to any available cell and copy it, then select your list and do a Paste Special, select Add. This will add 12 hours to convert to PM.

Then if you want to add 2 hours, enter =2/24 in a cell (2 hours divided by 24hrs/day) and do the same Paste Special, Add. If you want to go back 2 hours, select Subtract....

[sanram]

Say your time is on A1. Then write this formula on B1 :

Please Login or Register  to view this content.

This will do your job.

[cjenkins08]

The problem I see with "sanram's" suggestion is I need to convert 100's of times all at once.


"NBVC" suggestion sounds like what I need to do, but it is not working correctly for me.
Let's ignore the convert from AM to PM for now. It looks like all of the times are 4:00 or after, so I will not need to worry about going back to the AM on a 3 hour change.

So, I am just testing this out on the first cell A1 with a value of 8:30. I am clicking on B1 and pasting the =2/24, which it converts to 0.083333333, from there I copy the 8:30 and click paste special in the B1 cell choosing "subtract" and I am getting this number in B1 -0.270833333

What am I doing wrong?

[etaf]

The problem I see with "sanram's" suggestion is I need to convert 100's of times all at once.

you could just set up the 4 columns

in column b put
=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))

copy down

in column c
=TIME(HOUR(b1)-1,MINUTE(b1),SECOND(b1))
copy down

in column d
=TIME(HOUR(b1)-2,MINUTE(b1),SECOND(b1))
copy down

in column e
=TIME(HOUR(b1)-3,MINUTE(b1),SECOND(b1))
copy down

[NBVC]

Hmmm... I suppose for mine to work properly there would have to be a date component in your list, so that subtracting 2 hours from say, midnight on one date would give 10:00 PM on the previous date....

[cjenkins08]

Hmmm... I suppose for mine to work properly there would have to be a date component in your list, so that subtracting 2 hours from say, midnight on one date would give 10:00 PM on the previous date....

This is not a concern......All of the times are from 4:00 to 10:30 and the max I need to go back is 3 hours. So I am not worried about going to the previous day.

[cjenkins08]

you could just set up the 4 columns

in column b put
=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))

copy down

in column c
=TIME(HOUR(b1)-1,MINUTE(b1),SECOND(b1))
copy down

in column d
=TIME(HOUR(b1)-2,MINUTE(b1),SECOND(b1))
copy down

in column e
=TIME(HOUR(b1)-3,MINUTE(b1),SECOND(b1))
copy down

Thanks! I was able to get this to work!

[NBVC]

This is not a concern......All of the times are from 4:00 to 10:30 and the max I need to go back is 3 hours. So I am not worried about going to the previous day.

Then if you have 8:30 AM in A1 and =2/24 in another cell and copy it and do a Paste Special|Subtract you should get a result of 0.270833333 (positive not negative). Then you would need to format the result as Time.

[etaf]

you are welcome