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

[Rajatka]

Let say I have two cell with date and time together.

Start date and time- A1: "01 Feb 2013 03:35:33"
End date and time-B1:"05 Feb 2013 04:28:21"

What should I do to calculate the difference between with the result "days","hrs","mins".

If I calculate the difference B1 is greater than A1, then it should display as 4 days 7 hrs 28 mins

If A1 is greater than B1 and it should be in negative, suppose
Start date and time- A1: "05 Feb 2013 03:35:33"
End date and time-B1:"01 Feb 2013 04:28:21"
Result should be -4 days 7 hrs 28 mins

Please help me.

[Jakobshavn]

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

[amit.wilson]

Hi - try this in C1

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

Cheers

[daddylonglegs]

=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

[amit.wilson]

Yes, you are right daddylonglegs. Thanks for catching that one.

The correct formula is

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

Cheers

[Quiche IT]

This is almost exactly what I've been looking for. Is there any way to modify this formula for Net Workdays?

[amit.wilson]

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:

Please Login or Register  to view this content.

D1:

Please Login or Register  to view this content.

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

[Quiche IT]

Thank you!