Calculate Business Hours between today's date and Future Date

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
So I currently have a need to calculate the number of business hours (based on a 40 hour work-week) between the current date and a future date (date field "Completion Date"), not including weekends.

Is this a simple formula to pull-off? 

Thanks in advance for the assist.
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
re: "simple formula"

Someone posted this at one time on this forum.  If its your lucky day, it might work.  Let me know.
It probably works.



var timeofday DayStartTime = ToTimeOfDay("8:30 am");
var timeofday DayEndTime = ToTimeOfDay("5:00 pm");

var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
 
var DateTime EndDateTimeTesting = If(IsNull([Status End]), Now() ,[Status End]);

var DateTime EndDateTime = Min($EndDateTimeTesting, ToTimestamp(ToDate($EndDateTimeTesting), $DayEndTime));

var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime), ToDate($StartDateTime)) + 1; //(we count each day as a full workday) 

var number HoursBeforeStartEndAdjustment  = $WeekDayDays * ToHours($DayEndTime - $DayStartTime);

var datetime StartDateTimeBounded = 
  If(ToTimeOfDay($StartDateTime) < $DayStartTime, ToTimestamp(ToDate($StartDateTime), $DayStartTime), $StartDateTime);

var datetime EndDateTimeBounded = 
If(ToTimeOfDay($EndDateTime) > $DayEndTime, ToTimestamp(ToDate($EndDateTime), $DayEndTime), $EndDateTime);

Max(0,
Round($HoursBeforeStartEndAdjustment 
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime)) 
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded))),0.1))
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
Yikes...yea, simple, indeed.

Thank you for passing this one along - I've been tinkering with this, and know that I need to ID a start and end date, but that leaves me with a couple of questions that you might be able to answer for me:

1. The start and stop can be determined with two calendar fields (appropriately named), correct? In this instance, to follow the pasted coded, 'Status Start' and 'Status End'?

2. In line 4 of the code, there are two references to 'Status Start':

var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
 
Not being a coding wizard, I am assuming the first reference is to my calendar field 'Status Start'?  If so, what is the second reference after 'ToTimestamp'? 

Here is the error message that I get from that second reference:

The types of the arguments or the number of arguments supplied do not meet the requirements of the function ToDate.

The function ToDate can be used with the following arguments:
  • ToDate (Text x).
  • ToDate (Text x, Text f).
  • ToDate (Date/Time x).
Any insight/clarity would be greatly appreciated!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
In that example code, the [Status Start] field is a date/time type field.  Since you have asked for business hours, then the formula needs to know the start date and the start time.

Do you have fields for the start and end or do you currently have these as separate fields for the start date and the start time and the end date and the end time.
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
Late response (extended weekend).  Appreciate the guidance.

Yup, my calendar entry was simply 'Date'...and as soon as I read your reply reminding me of the hour aspect, I felt stupid.

I changed the field to 'Date / Time' and it works great - thanks again for the help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Great, thx for letting me that the formula works!
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb

You bet - thanks for passing on it on. 

One item that I have noticed is that you do have to be specific about logging the time (in addition to date).

For example, I have it set to calculate on a 30-hour work-week, so selecting the Monday on my project start time and Friday on my project end-time SHOULD calculate an even 30 hours. (in the formula I have it set to 08:00 AM - 02:00 PM).

What I'm finding is, if I don't type in the actual time-stamp in the calendar fields (ie 06-04-2018 08:00 AM - 06-08-2018 02:00 PM) it will skew the numbers (even though the times are set in the formula).

Other than that little wrinkle, it works beautifully for me needs.