Date Calculation is giving wrong answer!

this is driving me crazy, it should be simple (and probably is to someone who knows the answer).

I'm trying to calculate lapsed time with two values and then format that result to something readable. For example
My start date is 20/02/2019 16:48:11 (except it appears at 2019-02-20 16:48:11 in the sheet but formatted this way when I copied the value)
My end date is 20/02/2019 16:54:05 (same format as my start date so it's not a mismatch I don't think)

So I expect my lapse time to calculate as
00 Months 00 Days 00 Hours 05 Minutes 54 Seconds (this is the readable format I used on the cells)

Except it actually show
01 Months 00 Days 00 Hours 05 Minutes 54 Seconds

Where is getting the 1 month from in the result ??

The underlying formula is =IF(M2="",NOW()-B2,M2-B2) - where M2 is the end date but used NOW() in case it was blank

Please, someone, stop my hair turning grey! I've hunted for the answer but am just stumped.


p.s Hello by the way, this is not actually my first post but I couldn't reset my password on my old account so created a new one.

don't remember having to post the workbook .. so pointer on how to do that

As you are subtracting dates, or more specifically the underlying sequential number that represents the date,the result will also be a sequential number that represents a date. So the 1 comes from the fact your formula result is, if you format the cell as per the others, the date '1900-01-00 00:05:54'. The 1 is for 'January' and is returned by the 'M' in the number format you have applied to the formula cell.

Take a look here for how to accurately subtract dates and times to get the elapsed result you require. Although this page is for an office add-in, the raw formula you are looking for is under the heading "Formula 5. Display time difference as "XX days, XX hours, XX minutes and XX seconds"

You can use the DATEDIF function to get the Months.

Please Login or Register  to view this content.

If the formula is getting a bit lengthy I would suggest using some additional columns and splitting each part of the formula out. Then just join it all back together for your result.