Looking for an Excel Formula

madrab

Hey guy

If i have a list of dates in this format dd:mm:yyyy hh:mm:ss
& another list in in the same format

is there a formula to find the difference between 2 of them in hours?

Thanks

fintan

Have you tried?

=date2-date1

It may gie you an answer in date format, but change the format of the "answer" cell to numbers

madrab

yeah but that gives me a number answer that doesnt convert into hours

ie
Date1 24/03/2007 10:00:00
Date2 27/03/2007 07:00:00

date2-date2 = 2.875 in general form
= 02/01/1900 21:00:00 in date form

fintan

madrab wrote:

yeah but that gives me a number answer that doesnt convert into hours

ie
Date1 24/03/2007 10:00:00
Date2 27/03/2007 07:00:00

date2-date2 = 2.875 in general form
= 02/01/1900 21:00:00 in date form

But the answer is 2.875 days, so you need to construct a wee formula to convert that to hours?

Alternatively use a formula to separate (use the LEFT and RIGHT formulas)the date and the hours from each other and then take them away from each and then work it out

Another method would be to use the =DAYS360 formula but this will only count the days not the hours

h0stn0tf0und

Hey madrab,

I had to do this before,

I set the date cells to custom format dd/mm/yyyy HH:MM

I then used this formula
=CONCATENATE(LEFT((B2-A2)*24,FIND(".",((B2-A2)*24))-1),":",ROUND(RIGHT((B2-A2)*24,LEN((B2-A2)*24)-(FIND(".",((B2-A2)*24))-1))*60,0))

I round the minutes up.
I had a header row thats why I am starting from A2/B2

Might not be exactly what your after but it might help you on your way.

madrab

cool, thanks for the help