Determining Night Shift or Day Shift

[onkeypress]

We are doing some working hours calculation and we need to find out how many hours in the day and night shift. The day shift starts from 6:00 AM till 10:00 PM. The night shift starts from 10:00 PM till 6:00 AM.

For example:
Sign in time: | Sign Out | Total Hours | Day Hours | Night Hours
--------------------------------------------------------------------
8:00 PM | 2.30 AM | 6.50 | 2.00 | 4.50
--------------------------------------------------------------------
10:00PM | 8:15 AM | 10.5 | 2.50 | 8.00

How do I determine the range of hours worked includes in the day shift or night shift or both?

 

[FoFa]

I would either check your start time for being within a certain window (like 5:30 AM through 6:30 AM as an example) but you will have to figure the window that works for you. What if someone comes in at noon, then what?

 

[onkeypress]

Thanks Fofa for the suggestion but already did that. Of course it works but I was hoping for another kind of solution. Something that loops through the specified hours and determine whether each hour is in the night shift time zone or day shift time zone. But anyhow thanks.

 

[Shingouki]

Perhaps something like this, you could put these three functions into a query *the first is perhaps a bit unnecessary as a function you can just use DateDiff for the total working hours afterall*

Function total_hours(t_in, t_out)
  [COLOR="SeaGreen"]'calculate hours worked[/COLOR]
  hours_worked = DateDiff("n", t_in, t_out)
  hours_worked = hours_worked / 60 [COLOR="seagreen"]'convert to hours[/COLOR]
End Function

Then for the Day hours.

Function day_hours(t_in, t_out)

Day_In_Boundary = #6:00:00 AM#    [COLOR="seagreen"]'The earliest starting point for a day shift[/COLOR]
Day_Out_Boundary = #10:00:00 PM#   [COLOR="seagreen"]'The latest time employees may end a day shift[/COLOR]

[COLOR="seagreen"]'hours worked after 10.00pm not part of day shift[/COLOR]
  If t_out > Day_Out_Boundary Then
     t_out = Day_Out_Boundary
  End If
  
  [COLOR="seagreen"]'hours worked before 6.00am not part of day shift[/COLOR]
  If t_in < Day_In_Boundary Then
     t_in = Day_In_Boundary
  End If
    
 [COLOR="seagreen"]'calculate hours worked[/COLOR]
 day_hours = DateDiff("n", t_in, t_out)
 day_hours = day_hours / 60 [COLOR="SeaGreen"]'convert to hours[/COLOR]

End Function

Finally the night hours.

Function night_hours(t_in, t_out)

Night_In_Boundary = #10:00:00 PM#[COLOR="seagreen"] 'The earliest starting point for a night shift[/COLOR]
Night_Out_Boundary = #6:00:00 AM#[COLOR="seagreen"]'The latest time employees may end a night shift[/COLOR]

[COLOR="seagreen"]'hours worked after 06.00am not part of night shift[/COLOR]
  If t_out > Night_Out_Boundary Then
     t_out = Night_Out_Boundary 
  End If
  
  [COLOR="seagreen"]'hours worked before 10.00pm not part of night shift[/COLOR]
  If t_in < Night_In_Boundary Then
     t_in = Night_In_Boundary 
  End If
    
 [COLOR="seagreen"]'calculate hours worked[/COLOR]
 night_hours = DateDiff("n", t_in, t_out)
 night_hours = day_hours / 60 [COLOR="SeaGreen"]'convert to hours[/COLOR]

End Function