Advanced- How do I calculate the difference in dates & times In Excel 2010?

Question

I have a table inserted in a worksheet with the following:

A1 is 5/19/13 12:00 AM and D1 is 5/20/13 2:00 AM.

B1 & C1 use the same the function = IF(ISNUMBER(D1), IF(ISNUMBER( A1), NETWORKDAYS.INTL(A1,D1,7, HOLLS)-1, "")) and result is 24:00
(custom format is [h]:mm), and 1.0 (number format) respectively.
The exact results should be 26:00 and 1.08 respectively and not rounded up to whole numbers as previously mentioned.

Instead of 24:00 (custom format is [h]:mm), and 1.0 (number format) as results in two cells, It should be 26:00 & 1.8 respectively.

Any solutions to this?




Many Thanks.

Answer 1

The problem is that the networkdays function returns only whole numbers, so you are losing the hours in the calculation. Once you have whole days, excluding holidays and weekends, you can add the hours:

= IF(ISNUMBER(D1), IF(ISNUMBER( A1), NETWORKDAYS.INTL(A1,D1,7, HOLLS) + MOD(D1 - A1, 1), ""))



The expression MOD(D1 - A1, 1) returns the fractional part of the difference betweeen D1 and A1

Answer 2

Just use simple subtraction:

B1 =D1 - A1

C1 =D1 - A1



B1 is formatted as [h]:mm, while C1 is formatted as a number with two decimal places (0.00 or 0.##).



Excel stores dates and times as special numeric values that allow for regular math operations and flexible formatting.



Keep in mind that any negative result will be displayed as ####### when using date/time formats. After all, we can't have "negative January" or the like.



Do you need NETWORKDAYS.INTL() for any reason?

Answer 3

as long as the two dates are in Excel criminal date format, you may merely subtract one date from the different. the end result would be varied days. you may desire to alter the format of the end result cellular to a important decision format (i.e. no longer date format). case in point if the two dates are in A1 and A2, the formula you want would be =A2-A1