Calculate duration excluding weekends, dependent fields are date/time formatted, present result in hours.

The WeekdaySub function uses Date fields - so if you want to use that, you will end up with a result rounded to the nearest day (meaning, the result is no longer "to the hour").

If you want to keep the result in hours - then you have to account for more cases - what happens if the [Deadline] or [Time Completed] falls on a weekend?  How would you convert the "days" result from "WeekdaySub" to hours?  (count each day as 8 hours?  or 24 hours?)  How the to account for the "hours" based on the time?  Gets complicated.

In my opinion, probably more straightforward to keep it to "days" - if possible for your use case.

Xavier - I really appreciate you taking the time to respond to my question.  Let me give a bit more context around my problem in an effort to show why I need to calculate this in hours.

I am an accounting manager at a mid-sized private company.  We are implementing QuickBase to manage our month end close process every month.  Each month end close checklist should be the same as the prior month, with small changes being made as necessary.  You can think of each close as a project with multiple people interacting as process/task owners in that project.

The heavy portion of our month end close project lasts one week and goes from Monday-Friday - we must close on Friday, so our deadlines will never fall on a weekend.  You might then ask why I need to exclude weekends from the calculation.  Well, we have dependent tasks that occur in the week prior to our heavy week of month end close and those sometimes spill into the heavy week.  I want to measure those as being behind schedule but I don't want to take on the weekend hours.  

Due to the short period of time, it is really important for our team to be able to manage if dependent tasks are falling behind schedule down to the hour level.  Measuring whether something is behind schedule at the day level would not give us the level of detail that we need to monitor the project's status.

Given that, is there a solution to solving for this in hours?

Try this for the formula - for the Formula - Numeric field:



var DateTime StartTime = If([Task Status]="Completed", [Time Completed], Now() );

var Number HoursIncludingWeekends = ToHours( [Deadline] - $StartTime );


var Number NumWeekendDays = ToDays( ToDate([Deadline]) - ToDate($StartTime) ) - WeekdaySub ( ToDate([Deadline]), ToDate($StartTime) );


$HoursIncludingWeekends - ($NumWeekendDays * 24)




The first line calculates StartTime - which would be [Time Completed] if [Task Status] = "Completed", else it's the current time (Now() ).  This saves having to repeat the calculation again further down.

HoursIncludingWeekends is calculated next - as the total number of hours between [Deadline] and StartTime.


NumWeekendDays calculates the number of weekend days between [Deadline] and StartTime.  It does this by converting all the Date/Times to Dates (with the ToDate() function), then figuring out how many days there are between them, and finding the difference when you exclude weekend days using the WeekdaySub() function.


Finally - we give the final result which is simply the total number of hours (HoursIncludingWeekends) and subtracting the number of weekend hours (which would be NumWeekendDays * 24).

Hope this helps!

See below for my pass at this.

Xavier - I had no idea you could define new variables in functions...this just opened a ton of new doors for me.  Thank you so much!

I have another issue to tackle when I roll the pilot of the project management app out from pilot mode to worldwide use.  We have folks in other states/countries that work on our close as well.  I will need to find a way to factor in time zone into all of my fields and calculations...that will be a doosy.  When I get there, I may seek you out again.

Glad I could help!  Feel free to ask further questions.

I've been looking for a solution to the Excel equivalent of the networkingdays() function and this is it.  Probably easy for some folks but I'll copy and paste the code in so it shows up in searches.  (In my formula booked date = start date and scheduled date = deadline


var Date StartTime = [Booked Date];
var  Number  NumWeekendDays = ToDays([SCHEDULED DATE] - ($StartTime))  - WeekdaySub ( [SCHEDULED DATE], ($StartTime) );

If([XMF Current Production Schedule Date]>[SCHEDULED DATE],ToDays([XMF Current Production Schedule Date]-[Booked Date])-($NumWeekendDays),ToDays([SCHEDULED DATE]-[Booked Date])-($NumWeekendDays))