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

#### KristaRN

##### New Member
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!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

try this

=B2+47/60/24

#### gebobs

##### Board Regular
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

##### New Member
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

##### Well-known Member

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

##### New Member
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

##### New Member

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

##### Well-known Member
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

##### New Member
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

##### New Member
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."

Replies
0
Views
163
Replies
1
Views
100
Replies
1
Views
518
Replies
0
Views
373
Replies
0
Views
357

1,136,864
Messages
5,678,210
Members
419,751
Latest member
richkings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back