Here is the formula that I have now, that works, that is not excluding weekend days:
ToHours(If([Task Status]="Completed",[Deadline][Time Completed],[Deadline]Now()))
Here is what I tried to do to modify it but it isn't working:
ToHours (If([Task Status]="Completed",WeekdaySub ([Deadline],[Time Completed]),WeekdaySub ([Deadline],Now())))
 20 Points
Posted 4 years ago
Xavier Fan, Champion
 800 Points
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.
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.
 20 Points
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 midsized 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 MondayFriday  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?
I am an accounting manager at a midsized 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 MondayFriday  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?
Xavier Fan, Champion
 800 Points
Xavier Fan, Champion
 800 Points
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!
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!
 20 Points
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.
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.
Xavier Fan, Champion
 800 Points
 602 Points
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))
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))
Related Categories

Forms
 3342 Conversations
 149 Followers

Formulas & functions
 3036 Conversations
 81 Followers

Relationships
 2670 Conversations
 51 Followers

Roles & permissions
 2259 Conversations
 25 Followers

Tables & fields
 7324 Conversations
 200 Followers