Substracting dates/times formula needed

[dcoates]

I saw a post @ http://www.excelforum.com/excel-gene...and-times.html that gave a formula for subtracting two dates/times. But when I try the formula I get an error of #VALUE!

The formula that was given follows:
=DAY(B1-A1)&" days "&HOUR(B1-A1-DAY(B1-A1))&" hours "&MINUTE(B1-A1-DAY(B1-A1))&" minutes "&SECOND(B1-A1-DAY(B1-A1))&" seconds "

Why am I getting an error message? My data is in A1 (3/14/15 8:00 AM) and in B1 (3/16/15 22:43 PM).


Thanks in advance.

[mcmahobt]

In B1, try this instead:

Please Login or Register  to view this content.

Military time doesn't have AM/PM associated with it in Excel, and that value seems to be throwing off the calculations.

[azumi]

not sure but try this:

=IF(INT(B1)-INT(A1)=0,"",INT(B1)-INT(A1)&" days ")&IF(HOUR((B1-INT(B1))-(A1-INT(A1)))=0,"",HOUR((B1-INT(B1))-(A1-INT(A1)))&" hours ")&IF(MINUTE((B1-INT(B1))-(A1-INT(A1)))=0,"",MINUTE((B1-INT(B1))-(A1-INT(A1)))&" minutes ")&IF(SECOND((B1-INT(B1))-(A1-INT(A1)))=0,"",SECOND((B1-INT(B1))-(A1-INT(A1)))&" second")

cheers

[AlKey]

Actually you don't need such a big formula for this. All you need: =B1-A1

then format cell as Custom, d " Days", h " Hours", m " Minutes", s "Seconds"

Row\Col	A	B	C
1	3/14/2015 8:00	3/16/2015	2 Days, 14 Hours, 43 Minutes, 0 Seconds

[dcoates]

Thank you all. The military time was what was giving me the issue. So even the simply formula worked. But ended up using the original formula from olechippy since it better fit my needs.