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

KristaRN

New Member
Joined
Jul 29, 2015
Messages
7
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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 "/".
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
ok, if

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

=B2+47/60/24
in C2

what do you get in C2?
 
Upvote 0
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.
 
Upvote 0
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."
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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?

Disable AdBlock

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
Back
Top