Exclude Weekends

[aenr]

Good Day WF Community!

I'm working on a report that will count the total number of hours spent on a task. My formula will be ({actualCompletionDate},{actualStartDate}) but I would like to exclude weekends hours from this count. How do we do that in text mode? Thank you in advance!

aggregator.displayformat=compound
aggregator.function=SUM
aggregator.namekey=datevariance
aggregator.valueexpression=ROUND(PROD(DATEDIFF({actualCompletionDate},{actualStartDate}),24),0)
aggregator.valueformat=HTML
description=Total Task Hour
displayname=Total Task Hour Duration
namekey=datevariance
shortview=true
textmode=true
valueexpression=ROUND(PROD(DATEDIFF({actualCompletionDate},{actualStartDate}),24),0)
valuefield=dateVariance
valueformat=HTML

[Richard_Le_ · Accepted solution]

Hi,

Try the below:

description=Total Task Hour
displayname=Total Task Hour Duration
textmode=true
valueexpression=ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),60),2)
valueformat=HTML

This value expression will calculate the number of working minutes (as dictated by your default schedule) between the actual start and completion date of a task. It will then divide the number by 60 to return the number of hours. Then it will round it to two decimal places.

Best Regards,

Rich. 

[Heather_Kulbacki]

As Rich said, his expression will give you the working minutes as dictated by your default schedule. If you need to calculate those working minutes by another schedule in your instance try:

valueexpression=ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate},"Your Schedule ID"),60),2)

or if you need your report to work with various schedules that may be attached to any project in your report try:

 valueexpression=ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate},{project}.{scheduleID}),60),2)

[aenr]

Thank you @Heather_Kulbacki and @Richard_Le_ both are working like a charm. I have the following question: how do I do this if I want the number of days to show instead of hours and exclude weekends?

[Richard_Le_]

No problem, glad I could help!

In the current value expression, we are dividing WORKMINUTESDIFF by 60 to return the number of hours (since there are 60 minutes in an hour). If we wanted to return the value in days, we would simply need to update the math to divide the value by the number of minutes in your working day.

As an example, if your working day is 8 hours (which equates to 480 minutes), the value expression would look like this:

ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2)

Best Regards,

Rich