Calculate Duration (days) between two dates but only calculate weekdays.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Calculate Duration (days) between two dates but only calculate weekdays.  Eg. Task starts on Friday and completes on Monday.  It should be 2 days, not 4.  Omit weekend days.  

this does not work:

WeekdaySub([Site Plan Requested],[Site Plan Returned])
Photo of B

B

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Can you explain why the WeekDaySub is not working for you?  Can you tell us an example values for the two dates fields and the result and the result that you expect?

Note that you may need to add 1 to the result to get the result you expect.  For example

e.g. 2-1 =1
similarly, e. g. Tuesday - Monday = 1 day.  But you may want to call that 2 days if you count both the Monday and the Tuesday as a Day.
btw, you probably want to reverse the date fields to get a positive number

WeekdaySub([Site Plan Returned], [Site Plan Requested])
Photo of B

B

  • 20 Points
WeekdaySub([Site Plan Returned] , [Site Plan Requested]) is not working, the formula field saves this formula with no errors but produces no result when dates are added to the two fields in the formula.   Just a blank field.
what field type are those two fields being used in the formula?
Photo of B

B

  • 20 Points
I have two dates fields side by side in a form.  Site Plan Requested and Site Plan Returned.  I have a field beside these called "Days to Complete" that calculates the duration.  Right now the field is using a simple operator (-) to handle the duration calc, but it includes weekends.  So when I add a date in both fields with the weekdaysub formula, the Days to Complete field is blank.
The field type for the WeekDaySub field needs to be formula numeric.  It might now be set to formula Duration?
Photo of B

B

  • 20 Points
Ahhhh!  It is always so simple.  That was it, it was a formula duration.  once I changed it to the numeric, viola!  you are the man Coach!  So, lesson learned, if a formula field does not give an error but produces a blank result, the formula field type is wrong.  Thanks again!
Photo of B

B

  • 20 Points
Can you make that an Answer so I can check of that you solved my problem.
to be fair to you, that syntax editor should have thrown an error on that formula to give you a clue why it was not happy.
Its now an answer!
Photo of DanielCP

DanielCP

  • 0 Points
Hi Mark,
Im using this formula WeekdaySub(LastDayOfMonth(Today()),FirstDayOfMonth(Today())) and the result is 21 workdays not 22 for January. Are there any exceptions to the rule? Thanks
Can you answer me this question?  Are counting fences or fence posts?

or put another way

Let's say that weekends aside, how many days are there in say January 2017? You will likely tell me that there are 31 days.

But if I were to take the 31st and subtract the 1st of the month, Let's see that is 31 - 1 = 30 days.  Yikes, someone stole a day from us.

Where did the missing day go?  Well like I said are we counting fences or fence posts?

You will need to "add 1" if you want to count both the first and last day of the period  in the weekdaysub (  )