Time Format

[Guest]

HI All,

I am working in transport co. We are using software which give a report when
vehicles start and when ignition off and this software export the report in
..csv format. I want to calculate the type of trip whether the trip is
Business or private, business hours start from 06:00 to 17:00, if a vehicle
start after 6:00 and ignition off before 17:00 then trip count as Buiness, if
start before 6:00 and stop before 17:00 then trip should be Private /
Business, and start after 17:00 and stop before 6:00 the trip count Private.

I am getting the date in dd/mm/yyyy hh:mm:ss format from software, how i can
calculate trip type whether its Business or private?

Help required


Thanks & Regards

Hassan

 

[Roger Govier]

Hi

With Start time in A2 and End time in B2
Private Hours
=MAX(0,6-HOUR(A2))+(MAX(0,((HOUR(B2)+(24*(DAY(B2)>DAY(A2)))-17))))
Business Hours
=MIN(17,(HOUR(B2)+(24*(DAY(B2)>DAY(A2)))))-MAX(HOUR(A2),6)