Date AND Time Shift in Excel

[grogger]

Hello,
I am new to the forum and thanks to all who made this possible. A great resource!

In Excel 2010, I have 2 columns with data like this (with many rows of time stamped data)
9/2/2015 13:9:7

I've discovered that the real-time clock of the device wasn't set correctly, and thus the time and date is incorrect. However, I do know the correct date and time the first value should be set to:
9/26/2015 11:56:0

After I change the first date/time to match the correct date and time, how can programmatically set the 2nd and subsequent values so that they are corrected accordingly?

Thank you for helping me with this!

Regards,
Gary

 

[Jonmo1]

Welcome to the board.

Assuming that the difference for all the wrong date/times vs their correct date/times is the same (each off by 23 days 22 hours 46 minutes and change)

Take 2 cells.
The first cell that that is wrong, but you know what the correct time should be.
Say A1

In an available cell (say B1), put in the date/time that A1 should be corrected to.

In another cell, say C1, put the formula
=B1-A1

COPY C1

Highlight the whole range of incorrect date/times
Right Click - Paste Special - Values - Add - OK.

The ADD is the critical part
You have to actually click on Paste Special, it's not in the fancy slide out box when you just hover over paste special.

 

[grogger]

Hi, thanks!
Currently date and time are in separate cells. Should they be concatenated since the month transitions from Aug to Sept? Also, time is in 24hr format. Do you see any issue there?

Gary

 

[Jonmo1]

So if the wrong dates and times are in columns A and B respectively
Add a helper column (Say C) and put
=A1+B1

Then follow the same steps as before, but paste the values onto column C.
(you may have to convert C to values first with copy/pastespecial/values)

Then you can split the resulting values back into seperate cells with
=INT(C1) for the Date
=MOD(C1,1) for the Time.

 

[grogger]

I take it all back....got it *after I read* what you wrote in your last post. Thanks!

 

[Jonmo1]

You're welcome