Discussions

Expand all | Collapse all

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

QuickBaseCoach Dev./Training11-11-2016 16:21

QuickBaseCoach Dev./Training11-11-2016 16:31

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

    Top
    Contributor
    Posted 11-11-2016 14:48
    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])


  • 2.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 15:03
    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.


  • 3.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 15:06
    btw, you probably want to reverse the date fields to get a positive number

    WeekdaySub([Site Plan Returned], [Site Plan Requested])


  • 4.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Top
    Contributor
    Posted 11-11-2016 16:18
    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.


  • 5.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 16:21
    what field type are those two fields being used in the formula?


  • 6.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Top
    Contributor
    Posted 11-11-2016 16:22
    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.


  • 7.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 16:24
    The field type for the WeekDaySub field needs to be formula numeric.  It might now be set to formula Duration?


  • 8.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Top
    Contributor
    Posted 11-11-2016 16:28
    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!


  • 9.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Top
    Contributor
    Posted 11-11-2016 16:29
    Can you make that an Answer so I can check of that you solved my problem.


  • 10.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 16:30
    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.


  • 11.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 11-11-2016 16:31
    Its now an answer!


  • 12.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Top
    Contributor
    Posted 01-03-2017 16:51
    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


  • 13.  RE: Calculate Duration (days) between two dates but only calculate weekdays.

    Posted 01-03-2017 17:11
    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 (  )