Time Format
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)
--
Regards
Roger Govier
"Hassan" wrote in message
...
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
|