# Formula to add minutes to date+time in Excel

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.

• 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] Sep 24 '13 at 16:26

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.

• 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 . . . Sep 24 '13 at 17:41