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?

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

1 Answer

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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