Calculate difference between two dates and times in "days, hrs, mins

Are the cells containing the inputs text values or numbers formatted as dates?
Do you want the seconds truncated or rounded?

Originally Posted by amit.wilson

=IF(SIGN(B1-A1)=-1,"-","")&DAY(ABS(B1-A1))&" days "&HOUR(ABS(B1-A1))&" hrs "&MINUTE(ABS(B1-A1))&" mins"

That's good amit.wilson........but if the difference in days is > 31 then you get incorrect results using DAY function, better to replace that with INT

Q - got sucked into this and ended up with this very ugly formula:

A1: 01/02/13 Fri 3:35 AM

B1: 05/02/13 Tue 4:28 AM

C1:

Formula:

=MAX(0,(NETWORKDAYS((WORKDAY(A1,1*SIGN(B1-A1))),(WORKDAY(B1,-1*SIGN(B1-A1))))))+SUM((IF(WEEKDAY(A1,2)>5,0,(WORKDAY(A1,1*SIGN(B1-A1)))-A1))+(IF(WEEKDAY(B1,2)>5,0,B1-(WORKDAY(B1,-1*SIGN(B1-A1))))-INT( IF(WEEKDAY(B1,2)>5,0,B1-(WORKDAY(B1,-1*SIGN(B1-A1)))))))

D1:

Formula:

=TEXT(C1,"d:hh:mm:ss")

Here's the logic (in case something more elegant is possible)

- count full workdays between [start of next business day after A1] and [end of previous business day before B1]. These are full days. No hours, min or sec to be counted.

- count front stub of hours,min,sec. From [start time A1] till [start of next business day after A1].

- count end stub of hours,min,sec. From [end of previous business day before B1] till [end time B1].

- Either or both stub are zero if either or both dates (A1, B1) are on a weekend, i.e. Sat, Sun. IF(WEEKDAY(A1,2)>5,0

- Have put in the SIGN(B1-A1) formula to allow for negative calculations, i.e A1 date comes after B1.

- As C1 is already so long and ugly, have used D1 to format the result.

Cheers