adjust existing column of time stamps by one hour

[will40]

Hello Everyone

I was hoping this was an easy task but I am struggling with it and could use your help.

We had a power monitor setup last month that captured and logged data every two minutes to an excel file. My problem is the monitor's time was accidently setup one hour ahead of the actual time.

Is there a way I can change all 6000 rows of column B time stamps to deduct one hour? Example attached. I need to adjust start from 10:08:52AM to 9:08:52AM and continue with the entire column.

Thanks for any help!

Will

[AliGW]

In another column:

=B1-0.0416666666666667

Drag copy down, select, copy, paste values.

[Pete_UK]

In a blank cell somewhere, enter 1:00:00 and then select that cell again. click Copy (or CTRL-C), then move your cursor and select all the cells in column B which have those times in, then right-click and choose Paste Special, and click on Values and on Subtract then OK and press the Esc key.

Hope this helps.

Pete

[will40]

In another column:

=B1-0.0416666666666667

Drag copy down, select, copy, paste values.

AliGW thank you works great, for almost every row. Problem I have when it gets to the midnight hours (00:00) it won't convert to 23:00 or 11:00PM. It converts to -0.0410648148148148

Do you have a trick for those 18 days?

Thanks

Will

[will40]

In a blank cell somewhere, enter 1:00:00 and then select that cell again. click Copy (or CTRL-C), then move your cursor and select all the cells in column B which have those times in, then right-click and choose Paste Special, and click on Values and on Subtract then OK and press the Esc key.

Hope this helps.

Pete

Pete

Thank you, you're solution is also so simple and works perfectly except for my time format. Like AliGW's formula it converts 00:00:00 to a decimal

Thanks

Will

[Pete_UK]

You need to subtract the value 1 from the date column and to add 1 to the time column for those instances where the time shows as a negative value. Again, you can use the same method that I proposed earlier.

Hope this helps.

Pete

[will40]

It appears it will not convert existing 00:00:00 time to 23:00:00 but it does convert existing 1:00:00 to 00:00:00 if that helps?

Thank you for your help!

[will40]

You need to subtract the value 1 from the date column and to add 1 to the time column for those instances where the time shows as a negative value. Again, you can use the same method that I proposed earlier.

Hope this helps.

Pete

thanks Pete. I will give it a try

[will40]

File is now repaired. Thank you both for your time and solutions!

Will

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Pete_UK]

Thanks for the rep, Will - glad to hear you got the file corrected.

Pete