Jump to content

Night time hours calc, which can span midnight


This topic is 2907 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've googled far and wide and have not been able to find anything that gives clues to solving this issue. I'm trying to write a calculation which returns the number of "night time hours" for a given Punch record, which can start and/or end inside of or outside of pre-determined "night time" hours. 

Our data:

A) A Punch record has two timestamp fields: ClockIn, and ClockOut. Any valid timestamps can be entered. Typically, the time stamps will be on the same day, different times, but, can also span from one day until the next day, bridging the midnight hour.

B) We know the time that we want to set as the beginning of "night time": For this exercise, let's set it at 9:00 PM, which is unchanging, can be hard-coded.

C) We know the time that we want to set as the end of "night time": For this exercise, let's set it at 5:00 AM, which is unchanging, can be hard-coded.

 

Our Scenarios. For simplicity, I'll use plain English whole numbers for times:

1) ClockIn = 10AM. ClockOut = 5PM. Calc should return "0", as none of those hours qualified for "night time" hours. 

2) ClockIn = 3AM. ClockOut = 11AM. Calc should return "2", because two of those hours were during "night time" hours.

3) ClockIn = 6PM. ClockOut = 1AM the next day. Calc should return "4" because four of those hours were during "night time" hours.

4) ClockIn = 3AM. ClockOut = 11PM. Calc should return "4" because four of those hours were during "night time" hours

5) ClockIn = 3AM. ClockOut = 1AM the next day. Calc should return "7" because seven of those hours were during "night time" hours.

 

Scenarios #4 and #5 have defeated me. I believe it has something to do with the fact that we have two separate groups of "night time" hours separated by a chunk of not-night-time hours. The phrase "recursive function" comes to mind, but not really sure, perhaps a really healthy LET and CASE without recursion?

Link to comment
Share on other sites

The two fields in a Punch record are timestamps, yes. My bad for over-simplifying the scenarios using plain English whole numbers for times. Here are the example scenarios again, this time using actual timestamps:

 

1) ClockIn = 01/01/2014 10:00:00. ClockOut =  01/01/2014 17:00:00. Calc should return "0", as none of those hours qualified for "night time" hours. 

2) ClockIn =  01/01/2014 03:00:00. ClockOut = 01/01/2014 11:00:00. Calc should return "2", because two of those hours were during "night time" hours.

3) ClockIn = 01/01/2014 18:00:00. ClockOut = 01/02/2014 01:00:00 (the next day). Calc should return "4" because four of those hours were during "night time" hours.

4) ClockIn = 01/01/2014 03:00:00. ClockOut = 01/01/2014 23:00:00. Calc should return "4" because four of those hours were during "night time" hours

5) ClockIn = 01/01/2014 03:00:00. ClockOut = 01/02/2014 01:00:00 (the next day). Calc should return "7" because seven of those hours were during "night time" hours.

 

It is not known wether or not one period can exceed 24 hours. Let's code on the assumption that no time period will exceed 23 hours, 59 minutes, 59 seconds and a fistful of microseconds. Thank you for looking!

Link to comment
Share on other sites

Assuming there are at most two night periods to consider (the one that begins on the same day as ClockIn, and either the one before or the one after - depending upon ClockIn occurring before NightEnd or after), try the following (not tested very thoroughly) =

Let ( [
nightStart = Time ( 21 ; 0 ; 0 ) ;
nightEnd = Time ( 5 ; 0 ; 0 ) ;

N0 = Timestamp ( GetAsDate ( ClockIn ) ; nightStart ) ;
N1 = Timestamp ( GetAsDate ( ClockIn ) + 1 ; nightEnd ) ;

direction = Case ( GetAsTime ( ClockIn ) ≥ nightEnd ; 1 ; -1 ) ;
N2 = N0 + 86400 * direction ;
N3 = N1 + 86400 * direction
] ;
Max ( Min ( N1 ; ClockOut ) - Max ( N0 ; ClockIn ) ; 0 )
+
Max ( Min ( N3 ; ClockOut ) - Max ( N2 ; ClockIn ) ; 0 )
)

The result type should be Time.

 

This returns results similar to the ones in your examples, with the exception of #5 where it returns 6:00:00 - which I believe is the correct result.

  • Like 3
Link to comment
Share on other sites

Comment, you're a genius! That works perfectly and is far more elegant than I had envisioned the answer being. And you're right about #5 - which is exactly why it's better to let computers do the math. [=
Thank you very much for your assistance. Need to buy you a beer!

Link to comment
Share on other sites

direction = Case ( GetAsTime ( ClockIn ) ≥ nightEnd ; 1 ; -1 ) ;
N2 = N0 + 86400 * direction ;
N3 = N1 + 86400 * direction

 

I read this whole thing and I do not see where the 86400 comes from.  Can you explain this for me?  I think i understand the rest of it by looking at results in viewer individually but this figure throws me.  Thank you Comment.

Link to comment
Share on other sites

This is so good.  All of it fits.  So I wondered if time worked like dates and tried this:  Time( 0 ; 1440 ; 0 ) and it works also.  I am grateful to all of you for the variety.  I will never have a problem with time again because I actually understand this now.  


1440 I mean.

Edited by Charity
Link to comment
Share on other sites

This topic is 2907 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.