Hours Between dates & time

[CCkfm2000]

hi all...

i need to work out the hours between 2 dates and times, i've attached a sample copy for reference.

please help

thanks

[Bob Phillips]

=DATEVALUE(SUBSTITUTE(C5,".","/"))-DATEVALUE(SUBSTITUTE(A5,".","/"))-1+(1-B5)+D5

but it would be simpler if you used real dates.

[Simon Lloyd]

Very clever Bob, i have been fiddling with DateValue but as you pointed out the dates aren't in the correct format, i then tried a concatenation of the two adjacent cells to see if it would custom format as dd/mm/yyyy hh:mm:ss still no joy, i was scratching my head looking at if(and(or statements - well i had 30 mins to spare at work, a rareity these days!

Regards,
SImon

[CCkfm2000]

the dates are extracted from SAP.

will look into it when i get back after the weekend now.

thanks and have a good weekend.

[Bob Phillips]

Very clever Bob, i have been fiddling with DateValue but as you pointed out the dates aren't in the correct format, i then tried a concatenation of the two adjacent cells to see if it would custom format as dd/mm/yyyy hh:mm:ss still no joy, i was scratching my head looking at if(and(or statements - well i had 30 mins to spare at work, a rareity these days!

Regards,
SImon

Actually, we can do it without DATEVALUE

=(SUBSTITUTE(C5,".","/"))-(SUBSTITUTE(A5,".","/"))-1+(1-B5)+D5

[Simon Lloyd]

I see.....just like extra typing or am i missing the reason for using DATEVALUE? i tried both versions of course no change to the outcome, however the solution effectively deducts Time A from Time B but the result should have shown 24hrs 47mins 36Secs rather than 00:47:36, i tried formatting as [h]:mm:ss to see if that gave the true amount of hours but gave a long serial number!

Regards,
Simon

[Wolfgang]

Hi Simon,

I was wondering about that result as well...then formatted it as:
37:30:55 in the Time Category...

Best,
Wolfgang

[Bob Phillips]

I used DATEVALUE to coerce the date string that was created out of the SUBSTITUTE. But, when you add or subtract from a date string, that does the coercing for you, obviating the need for DATEVALUE.

[Aussiebear]

Obviating is not a word a bear would use Bob.