How to calculate transit times?

Hi,
I need to calculate transit times in an excel spreadsheet. The data is pulled from a web site and comes in the format of a csv file which I open as an xls file.

Column A has the goods available date in the format of 01.05.2010
Column B has the goods available time in the format of 18:06:00
Column C has a collection date in the format of 01.05.2010
Column D has a collection time in the format of 23:12:00
Column E has a delivery date of 03.05.2010
Column F has a delivery time of 06:33:00

Question is how do I calculate the times taken from Goods Available to Goods Collected and Goods Collected to Goods delivered, express each in hours?

Thanks very much
Rgds
Andy.

Hello Andy,

If all the data was in valid date and time format then you can get available to collection with straight subtraction, i.e.

=D2+C2-B2-A2

then format result cell as [h]:mm to give total hours

but with dates shown as 01.05.2010 that probably won't be a valid format so you can convert those within the same formula, i.e. try

=D2+SUBSTITUTE(C2,".","/")-B2-SUBSTITUTE(A2,".","/")

Please see the attached sheet for solution

On further thoughts
Please find attached a new sheet with some modifications

I basically modified the formula by daddylonglegs

=VALUE(SUBSTITUTE(A2,".","/"))&B2 this is the formula that I have used to combine date and time into excel based values.

I have then subtracted Available Time from Collection Time and format it as time then converted it into hours using the following formula
=(HOUR(B6)*3600+MINUTE(B6)*60)/3600

Hope this solves your problem

Thanks very much for the kind help, it was as I suspected the formatting that was giving me the problems with the formula but the substitute command is not one that I have used before, but it does work well with this.

Thanks very much for all your help and assistance.

Kind rgds
Andy.