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())))
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.
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?
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!
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.
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))