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

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

=B2+47/60/24
 

gebobs

Board Regular
Joined
Oct 15, 2004
Messages
167
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 "/".
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jul 29, 2015
Messages
7
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
Joined
Jul 29, 2015
Messages
7
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."
 

Forum statistics

Threads
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?

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
Top