Need to add time to multiple cells containing date AND time of previously created spreadsheet

[KristaRN]

Hello,
I am Using Windows 7 and Excel 7, I have a previously created spreadsheet in which cell B2 contains the date AND time. It looks like this: 2015-07-17 12:55. There is no formula in the formula bar, and it is in a "general format" as best I can tell. (I did not create this spreadsheet.) The spreadsheet is populated from an upload from a piece of medical equipment. Unfortunately the time was off on the equipment by 47 minutes and I need to add that time back in to all of these cells (B2-B5621). Is this possible? This is a minute by minute spreadsheet for 72 hours of data.
Thanks!

 

[AlanY]

try this

=B2+47/60/24

 

[gebobs]

I take it the problem is that the date/time field is text and not actual date values. Is that right? When you have one selected, is there a preceding apostrophe (') in front of the date? Some data exports do this for some reason and it prevents Excel from recognizing and automatically converting the data to the proper format. If that's the case, the solution is easy. Create another column to add or subtract the 47 minutes from each cell. Since each day equals 1, 47 minutes is equal to 47/(24*60).

=B2+47/(24*60)

Excel should convert the text value to the date value for the purpose of calculating the result. If that doesn't work, try a find/replace. Replace each "-" with "/".

 

[KristaRN]

try this

=B2+47/60/24

Thank you Alan. I will try this solution. One question though...will I enter this formula in the entire column B cells beginning in cell B3?

Krista

 

[AlanY]

if your data is in B2-B5621, then should enter in C2, say and copy all down.

you can then copy and paste values only back to column B and delete column C, if wanted

 

[KristaRN]

Yes, it is text. There is no preceding (') in front of any of the dates, either in the cells themselves or in the formula bar. Again, I did not create the spreadsheet, so is it possible that (') is hidden?
It does make a lot of sense to just add another column, enter the appropriate times in manually (+47) to the first cell and then each cell in the column to follow would add a minute, correct? For this to work, is the appropriate process to enter the correct time in C2, format it as time 00:00, then highlight all remaining cells in the column and enter =C2+1 as the formula or would I need to enter =C2+1(24*60)?
I am pretty new at Excel, so please forgive my ignorance.

 

[KristaRN]

if your data is in B2-B5621, then should enter in C2, say and copy all down.

you can then copy and paste values only back to column B and delete column C, if wanted

OK, I think I understand and will try that. Thank you.
K

 

[AlanY]

ok, if

2015-07-17 12:55
is in B2 and this

=B2+47/60/24
in C2

what do you get in C2?

 

[KristaRN]

OK, I think I understand and will try that. Thank you.
K

OK, I tried entering the formula =B2+47/(24*60) into C2 and got the following error message. #VALUE! with a message that says, "a value used in the formula is of the wrong data type.)

I am going to try creating a new column C and enter the first time I know should be there (+47 minutes) and then add one minute to each of the following cells and see if that works.

 

[KristaRN]

ok, if

2015-07-17 12:55
is in B2 and this

=B2+47/60/24
in C2

what do you get in C2?

Hi Alan,
Thanks. I got the same error message as with the other suggestion. #VALUE! "A value used in the formula is of the wrong data type."