How to subtract date and time

[dprice2000]

I have:

Cell A1 03/12/2009 22:28:00
Cell B1 04/12/2009 10:40:00

I need to find out how many hours between these two dates and times.

Also need to then use that result in another formula so using Custom [hh]:mm doesn't seem to work.

Any help?

Thank you

 

[rs2k]

Try;

=B1-A1

and format as '[h]:mm'

This gives 12:12, how do you mean that it is being used in another formula, and doesnt seem to work?

Can you explain a bit more?

Cheers
Colin

 

[dprice2000]

Thanks for the answer.

Once i've found how many hours, I want to create another formula to see if the amount is over or under 24. Just doesn't seem to work.

=IF(C1<24,"On time","Over")


Thanks,

 

[Greg Truby]

24 hours is one day so it's:

=IF(C1<1,"On time","Over")

Also - if your timestamps are as per above you're cool, i.e. the date's are there too. However if you're working with JUST times, without dates in them, then you need something like:


=(B1 < A1) + B1-A1<A1) p + B1-A1<>
To handle cases where you start at 10:00 pm and end at 3:00 am (assuming it's 3 am the next day).

 

[dprice2000]

Perfect !!! Thanks for your help guys..