Microsoft Excel Datetime difference

Question

I've got this table where I'm trying to find the difference between two dates in the format. I have three columns with dates in the format of 28/01/2015 21:43

Following is the table I have,

Created date    Start date  end date    time to start   time to start from create date  time to finish from created date
11/02/2015 12:46    11/02/2015 16:15    13/02/2015 17:21    00 days 03:29:07    02 days 01:06:27    02 days 04:35:34
17/11/2014 15:17    27/11/2014 21:43    20/01/2015 19:21    10 days 06:26:52    22 days 21:37:17    04 days 04:04:09
22/12/2014 09:38    22/12/2014 09:49    16/01/2015 04:02    00 days 00:11:31    24 days 18:12:48    24 days 18:24:19
25/11/2014 10:58    25/11/2014 11:35    13/01/2015 17:35    00 days 00:37:04    18 days 06:00:39    18 days 06:37:43
13/01/2015 09:03    13/01/2015 09:42    13/01/2015 12:27    00 days 00:39:37    00 days 02:44:59    00 days 03:24:36

I've calculated the difference between dates (last three columns)using the below formula,

=TEXT(C1-B1,"dd"" days ""hh:mm:ss") 

which works for most part, but not for ones with a start date in 2014 and an end date in 2015. It completely discounts December, it counts days only up to November and then jumps to January. (As seen in line 2 and 4)

Is there any way to stop this?

Answer 1

You are mashing together format masks that do not belong in the same string. You are using the dd day-of-the-month for what should be 0 the-number-of-days-between-two-dates. You are losing December because 49 days as the -day-of-the-month is literally the 18^th day of the next month.

Dates are simply 1 for every day past 31-Dec-1899. Time is a decimal portion of a day. Today happens to be 42,256 and today at noon will be 42,256.5.

The INT function can pull the date portion of a datetime and the MOD function can do the same for the time.

=TEXT(INT(C2)-INT(B2)-(MOD(C2, 1)<MOD(B2, 1)),"0 \d\a\y\s ")&TEXT(MOD(C2, 1)-MOD(B2, 1)+(MOD(C2, 1)<MOD(B2, 1)),"hh:mm:ss")

You cannot have both 0 and any of the time format masks in hh:mm:ss within the same format mask. Building two with the days as a integer and the time as a conventional time value then concatenating the two strings is the best bet.

Additionally, because you cannot have negative time, if the time subtraction is going to be negative you need to add a day (i.e. 1) to the result. The same adjustment must be made to the day subtraction.

      

Answer 2

First it seems that the title of the 4th column should be “time to finish from start date“ instead of “time to start from create date”

The formula you are applying gives the difference between two dates as a serial number where the integer part represents the date difference and the decimal part the time difference, then the TEXT formula formats that serial number taking only the days and the time. For example in the 2nd row of the data (excluding the header) the “time to finish from start date” (resulting difference of “Start date“ minus “Created date”) is 53.9008912037025 that in a date format would be something like 22/02/1900 21:37:17 then the TEXT formula only takes the days and the time thus resulting in 22 days 21:37:17.

Now to obtain the result you expect we need to split the days from the time and format the result as required using this formula:

=CONCATENATE(TEXT(INT(SUM(C3,-B3)),"00")," days ",
TEXT(MOD(SUM(C3,-B3),1),"hh:mm:ss"))

The INT formula takes the integer part of the serial number resulting from the difference of the dates The MOD gives the decimal portion of the serial number Use the TEXT formula to format the both parts as required then concatenate the parts.