14

If I have a cell formatted as date+time in Excel, how can I add to this in terms of minutes? It would need to work whether I was adding 20 minutes, or 2,500 minutes.

So, for example if I had this spreadsheet, what formula could I put in B5 to get a result of 2013-09-22 09:10?

Example Spreadsheet

I tried using =B4+TIME(0,B3,0), but it gives 2013-09-21 09:10 which is a day off. It works fine though if B3 is less than a day's worth.

  • 1
    the reason the TIME formula does not work is: The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999 [which means that after 24 hours have been added, it wraps around again - see what happens if you add 1440 minutes using this method] – SeanC Sep 24 '13 at 16:26
  • @SeanCheshire, thanks! I added that to my answer. – Drew Chapin Sep 24 '13 at 19:41
24

You would need to divide the number of minutes by 1440 (24 hours in a day × 60 minutes in an hour) and then add that to the date.

=B4+(B3/1440)

or if you want to be more verbose...

=B4+(B3/24/60)

The reason this works is that Excel date+time values are stored as a floating point decimal number representing the number of days that have passed since January 1st 1900. So one hour is essentially represented as 0.04166666666 (1 day ÷ 24 hours). If you further divide that by 60 (the number of minutes in an hour) then you will get the representation of minutes which is 0.00069444444 (1 day ÷ 24 hours ÷ 60 minutes).

The reason that =TIME(...) does not work as you expected is that it's return value is limited to less than 1 day (i.e.0 to 0.99999999999). Anything that would result in more than 1 day would wrap around again.

  • 1
    Depending on who the audience is for the spreadsheet, I know I've written the above as =B4 + B3/24/60 to try and make it more clear what the formula is doing . . . – ernie Sep 24 '13 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.