Expand my Community achievements bar.

How to get Days Late to calculate based on business days (following a specific schedule)

Avatar

Level 4

We have a corporate schedule set up that all of our projects follow. I'm running a Days Late project but it counts the non-working days. Is there a way to have it calculate based on a set schedule? At a minimum, to exclude weekends? I've been searching for a while and not finding anything. Thanks!

4 Replies

Avatar

Level 7

I don't know of a way to exclude holidays set up in an instance.  But you can exclude weekends with the WEEKDAYDIFF expression.

 

wf date expressions.jpg

Avatar

Community Advisor

Hi Michelle,

 

Appending the ID of your corporate schedule to the end of a WEEKDAYDIFF calculation would enable you to count the number of weekdays between two dates AND take into consideration your schedule (i.e., exclude public holidays). The calculation would look something like this:

 

WEEKDAYDIFF({plannedCompletionDate},{actualCompletionDate},"SCHEDULE ID HERE")

Best Regards,

Rich. 

Avatar

Level 7

Hi Rich,

 

I wasn't aware of the Schedule ID option.  I just tried it out in a report on a test project.  I deliberately scheduled a task at the end of November (during which our company closes two days for Thanksgiving).  I was also able to run a report to obtain our Schedule ID (and I verified that the two days in question are recorded as full days off in the schedule).

 

I have a task that starts on 11/21/22 and is due on 11/30/22.  I've got two calculated columns in my report:

 

WEEKDAYDIFF({plannedStartDate},{plannedCompletionDate})

 

WEEKDAYDIFF({plannedStartDate},{plannedCompletionDate},"SCHEDULE ID")

 

Both calculations are yielding the same value (7).  Any idea what's happening here?

 

Thanks. 

Avatar

Community Advisor

Hi Kristen,

 

I could have sworn that this worked with the WEEKDAYDIFF expression, but upon testing it myself it appears not. I've either made it up in my head, or something has changed.  Sorry about that!

 

This DOES however work with the WORKMINUTESDIFF expression. So, if you use the following, you should get a calculation taking into consideration the working minutes of your specified schedule:

 

WORKMINUTESDIFF({plannedStartDate},{plannedCompletionDate},"SCHEDULE ID")

This expression obviously returns a value in minutes, so to convert it into days you simply divide the value by the number of minutes in the working day associated with your schedule. For example, if you work 8 hours a day this equates to 480 minutes, so your expression would look like this:

 

WORKMINUTESDIFF({plannedStartDate},{plannedCompletionDate},"SCHEDULE ID")/480

Give it a test yourself and let me know how you get on.

 

Best Regards,

Rich.