Subtracting Dates

[Dilly]

Am trying to subtract one date from another to get a calculated answer in
days, hours and mins. One of the dates in question is start date of
08/06/2007 08:43 with a finish date of 21/11/2007 13:00. Is their a cell
format avaible to display the answer as 166 (04:17) i.e. number of days,
hours and mins it has taken to complete this job

Any help would be appreciated

 

[Mike H]

Maybe this

=INT(B1-A1)&" ("&TEXT(MOD(B1-A1,INT(B1-A1)),"hh:mm")&")"

Earlier date/time in A1, later date/time in B1

Mike

 

[Dilly]

Many thanks for below, which works for that specific start and end date,
however when i have a start date of 07/11/2007 12:29 and finish time of
07/11/2007 15:00 it gives an error "#DIV/0!" value instead of 0 days 2 hours
31 mins ??
Nearly there though i think.

Once again your assistance is greatly appreciated

 

[JP]

Put your start date and time in A5 (for example) and end date/time in
B5.

Here's the formula:

=(IF(INT(B5)-INT(A5)<1,24*(MOD(B5,1)-MOD(A5,1)),((INT(B5)-
INT(A5))*24)+24*(MOD(B5,1)-MOD(A5,1))))/24

Format as Custom: d "days," h "hours," mm "minutes"

See http://tinyurl.com/2wu8ya for more assistance.

HTH,
JP

 

[Dilly]

Thanks for below, however this comes back to the problems i experienced as
described in my original post with a start date of 08/06/2007 08:43 and a
finsh date of 21/11/2007 13:00 shows as 14 days 4 hours and 17 minutes, when
it should be 166 days 4 hours and 17 minutes

 

[Mike H]

Dilly,

I was hoping to get away with that <g> try this, I'm afraid it's getting a
bit long

=IF(ISERROR(INT(B1-A1)&" ("&TEXT(MOD(B1-A1,INT(B1-A1)),"hh:mm")&")"),0&"
("&TEXT(B1-A1,"hh:mm")&")",INT(B1-A1)&"
("&TEXT(MOD(B1-A1,INT(B1-A1)),"hh:mm")&")")

Mike

 

[Dilly]

It maybe long but it seems to work fine,

Many Thanks

 

[Mike H]

Your welcome and thanks for the feedback

It maybe long but it seems to work fine,

Many Thanks

 

[David Biddulph]

Why TEXT(MOD(B1-A1,INT(B1-A1)),"hh:mm") ?
If you wanted the MOD, shouldn't it be MOD(B1-A1,1)? But "hh:mm" will do it
for you anyway so =INT(B1-A1)&" ("&TEXT(B1-A1,"hh:mm")&")" would do.

 

[Mike H]

sometime the wood just cannot be seen for the trees <g>
a much better solution

 

[JP]

I missed the part where you are using dates in dd/mm/yyyy instead of
mm/dd/yyyy, but it looks like you got your solution.


--JP