PDA

View Full Version : Hours Between dates & time



CCkfm2000
02-09-2007, 07:27 AM
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
02-09-2007, 09:39 AM
=DATEVALUE(SUBSTITUTE(C5,".","/"))-DATEVALUE(SUBSTITUTE(A5,".","/"))-1+(1-B5)+D5

but it would be simpler if you used real dates.

Simon Lloyd
02-09-2007, 10:25 AM
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
02-09-2007, 10:29 AM
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
02-09-2007, 10:43 AM
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
02-09-2007, 10:53 AM
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
02-09-2007, 11:52 AM
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
02-09-2007, 11:56 AM
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
02-09-2007, 01:59 PM
Obviating is not a word a bear would use Bob.