Jump to content
Sign in to follow this  
SkylineR37_

Night time hours calc, which can span midnight

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?

Share this post


Link to post
Share on other sites

You say timestamps, but you only show us times - not sure if that's significant. Can one assume no period goes over 24 hours?

Share this post


Link to post
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!

Share this post


Link to post
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

Share this post


Link to post
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!

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Or put another way, that is the number of seconds in a day.

  • Like 1

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By John Kostenbader
      Consider a standard timesheet application with a parent "timesheet" table and a related "timesheet details" table connected with a one-to-many relationship. On a layout based on the timesheet table I have a portal that outlines the details for a two week pay period. One of the elements on the timesheet details is what type of entry is it (i.e. regular hours, support hours, etc.). I would like to be able to summarize the hours of each of those entries on the timesheet layout. I have been able to create a calculated field that does sum the total hours well and I thought if I made the calculation something like this:
      If ( Timesheet_Details::Type = "Regular" ; Sum ( Timesheet_Details::Hours_With_Lunch ))
      It would only summarize only those timesheet details that had "Regular" in the "Type" field. Alas it does not work. Not sure how to approach this. I tried a summary calculation but it does not allow for summarizing specific data (seems all or nothing). 
      Would appreciate some direction on this. Ideally I'd have three calculations on the timesheet layout for "Regular" hours, "Support" hours (which are calculated differently and "WFH" hours. 
       

    • By Andreas Ravichandran
      Hi,
      Firstly, thanks for the great product. I can't tell you how much it has sped up the solution process for our tutoring business. I have a few questions and issues:
      The Calendar Search appears not to be working, and my attempts to debug have been futile. For example, I add a ToDo called "Tutoring Session." When I search "tutoring" or "Tutoring" or "Session" or any combination thereof on the search string entry it comes up as empty search. I have gone into the script and everything seems to make sense, so perhaps I am using the search in a way it's not intended to be? I have been having issues connecting the projects module to the timesheets module. When I go to the Add Line Item I can add everything except for a project. The popover displays and I can enter everything. When I click the search icon on the project's field, the projects do show up (someone else had been having this issue before I saw). But after I click on one of the projects it doesn't go put it in the box on the popover itself. I hope this makes sense. I do not have this issue with the staff on the main data entry timesheets page. I noticed that in the relationships section of the data manager the timesheets section only connects to a staff table. Could this be the issue perhaps? I tried to fix it by adding a new table, but my attempts were futile.  Is there a way to connect timesheets to Calendar, or simply to have a ToDo connected to a timesheet as well? For our tutoring business, we would like to add tutoring sessions to the Calendar as a ToDo, but we also would like to be able to track the hours each tutor has worked in the most recent pay period with the timesheets functionality. Is there a way you can recommend modifying fmstartingpoint or an easy way to integrate them like this? Having the ability to just add timesheets to the Calendar would remove the need for ToDos at all, which are tricky because they don't allow length of time for meetings, only when they start. I saw in a previous post Richard said that the new timesheets would have the ability to add the functionality easily, which would be awesome. Is there a way to create basic charts off contact data to present to the customers in a module? I have considered modifying the estimates module to do just this, but it would be helpful if there were a better solution, since you guys know better. Thanks so much for any help! Help with #2 and #3 would be greatly appreciated, as our ability to create timesheet structures them around connecting them to projects and putting them on a central calendar. Again, thanks for the great starting solution as well, which has helped so much.
  • Who Viewed the Topic

    1 member has viewed this topic:
    anteas 
×
×
  • Create New...

Important Information

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