date timemicrosoft 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.
This will be a little convoluted but I think it should work. Note, I am using Excel 2007 for this, so some steps may be a little different.
After importing your file (post step 9 above) perform the following:
=TIME(0,F2,G2) (where column F is the Duration column, column G is the first new column and row 2 is the first row beneath the headers)This should produce output similar to thus:
So for your example the number of minutes is 548? You can do a simple subtraction to get total hours/minutes, so if you have start time/date in A2 and end time/date in B2 this formula....
=B2-A2
with result cell formatted as [h]:mm will give you 9:08
to get 548 just multiply by 1440 (number of minutes in a day), i.e.
=(B2-A2)*1440
format result cell as general
Best 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.or if you want to be more verbose...
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 is0.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.0to0.99999999999). Anything that would result in more than 1 day would wrap around again.