Adjusting part of a cell's data

[Akovisto]

I'm still getting a hang of excel, and have a question that'll hopefully save me a lot of time.
I have to bulk-edit several excel documents with 500+ lines each. They are playout logs for a TV network, but they were created off. Currently, they read to start at 00:00:00, and end at say 00:12:34 (or in excel terms, 00:??:??). Basically I need to batch change them to read as 1:??:??, the ?'s being digits already placed. Example: Right now I have a sequence which reads it'll start at 00:32:54, and end at 00:41:22. I need to change this to 1:32:54/1:41:22. What is the most effective way to go about doing this, other than manually editing each value? Thank you in advanced.

[Paul]

Hi Akovisto,

If your cells contain time values in the format hh:mm:ss (e.g. 00:32:54) then you should be able to use PasteSpecial > Add to adjust all of them by 1 hour. In an empty cell type the following formula and press Enter:

=1/24

Copy that cell, then select your 500 cells with time values and right-click any of them and choose PasteSpecial > Add > OK.

That should add 1 hour to every time value you had selected.

[Akovisto]

Paul,

thank you for that. Spent the last two hours looking at all these complicated formulas, yet that was so simple (though doing the =1/24 turned my times into weird decimals, so I simply did it with 1:00:00 instead). Thank you for your help, made my day a whole lot easier.

[Paul]

Glad it helped.

The "weird decimals" are how Excel actually stores time values. 0 equals midnight, 0.5 is noon, 0.75 is 6:00pm, etc. up to .9999884 for 11:59:59 PM. (There are 86,400 seconds in a day, so each second is 1/86400th of a day.)

After adding 1/24, you could have re-formatted the cells to Time and it should have appeared correctly, but adding 1:00:00 works just as well as you've seen.