date and time calculations

[Pjcan1]

I have four fields in a table:

Open date - format dd/mm/yy
Open time - format HH:MM
Close date - format dd/mm/yy
Close time - format HH:MM

I'd like to calculate in the fifth field the difference, in hours, between the open date & time and the close date & time. How am I best to do this?

Also, if i wanted to apply a working day rule to this (of 09:00 to 18:00) how could I then calculate this in days / hours.

[DonkeyOte]

The key question is whether or not you need the working day element - if you do then the approach is significantly different.

Total duration:

E2:
=SUM(C2:D2)-SUM(A2:B2)
format as [h]:mm:ss

Working Day duration:

F2:
=(NETWORKDAYS(A2,C2)-1)*("18:00"-"9:00")+IF(NETWORKDAYS(C2,C2),MEDIAN(D2,"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS(A2,A2)*B2,"18:00","9:00")
format as [h]:mm:ss

use of NETWORKDAYS pre XL2007 requires activation of the Analysis ToolPak (tools - addins)

It would be helpful if you specified which version you're running either in post or profile [better] - and also your locale.