Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Format
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to type format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |