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

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" wrote:

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

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

"Mike H" wrote:

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" wrote:

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

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

On Jan 28, 9:22*am, Dilly wrote:
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

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

"JP" wrote:

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

On Jan 28, 9:22 am, Dilly wrote:
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

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" wrote:

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

"Mike H" wrote:

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" wrote:

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

It maybe long but it seems to work fine,

Many Thanks


"Mike H" wrote:

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" wrote:

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

"Mike H" wrote:

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" wrote:

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

Your welcome and thanks for the feedback

"Dilly" wrote:

It maybe long but it seems to work fine,

Many Thanks


"Mike H" wrote:

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" wrote:

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

"Mike H" wrote:

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" wrote:

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

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.
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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

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

"David Biddulph" wrote:

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.
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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

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

On Jan 28, 9:56*am, Dilly wrote:
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



"JP" wrote:
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"

Seehttp://tinyurl.com/2wu8yafor more assistance.

HTH,
JP

On Jan 28, 9:22 am, Dilly wrote:
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- Hide quoted text -

- Show quoted text -