Exclude Weekends | Community
Skip to main content
A
A_EN
Level 4
February 14, 2023
Solved

Exclude Weekends

  • February 14, 2023
  • 2 replies
  • 1173 views

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

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Richard_Le_

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. 

2 replies

R
Community Advisor
Richard_Le_Community AdvisorAccepted solution
Community Advisor
February 14, 2023

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
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    February 14, 2023

    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)

      A
      A_ENAuthor
      Level 4
      February 15, 2023

      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?

      R
      Community Advisor
      Richard_Le_Community Advisor
      Community Advisor
      February 15, 2023

      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

       

        Terms & ConditionsAccessibility statement

        Loading footer...