Variance Between 2 Dates (Weekdays Only)

Does anyone have a variance Formula between 2 dates that only counts the weekdays and not the weekends?

Here is my Current Formula which Counts All 7 Days:

=IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Finish Date (Planned)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF([Finish Date (Planned)]@row - [Finish Date (Actual)]@row < 0, ABS([Finish Date (Planned)]@row - [Finish Date (Actual)]@row) + " Day(s) Behind Schedule", IF([Finish Date (Planned)]@row - [Finish Date (Actual)]@row > 0, ABS([Finish Date (Planned)]@row - [Finish Date (Actual)]@row) + " Day(s) Early", IF([Finish Date (Planned)]@row - [Finish Date (Actual)]@row = 0, "0 Variance, On Schedule")))))

Best Answers

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭
    Answer ✓

    Something is out of Order in the formula. Take a look at Finish Date (Planned)10/25/19 and Finish Date Actual 12/13/19. The result is 35 Day Early instead of 35 days late. I can share the sheet if you like?


    =IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Finish Date (Planned)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 < 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 > 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1) + " Day(s) Early", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 = 0, "0 Variance, On Schedule")))))

«1

Answers

  • Alejandra
    Alejandra Employee

    Hi @Ray B,

    Have you considered using the NETWORKDAYS function? It returns the number of working days between two dates. If needed, more information on this can be found in our help article: https://help.smartsheet.com/function/networkdays

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Yes, I tried, but I would get an Error. I am not sure where to place it in my formula

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can place it anywhere in your above formula where you have "Date - Date". Simply replace "Date - Date" with NETWORKDAYS(date 1, date 2).


    Obviously you will need to update to reflect actual cell references as you have in your formula, but that's where you would work it in and how.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Yes, I tried, but I would get an Error. I am not sure where to place it in my formula


    =IF(ISBLANK([Actual Date]@row), "", IF(AND(ISBLANK([Planned Date]@row), ISDATE([Actual Date]@row)), "Start Date Missing", IF([Planned Date]@row - [Actual Date]@row < 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Behind Schedule", IF(NETWORKDAY(([Planned Date]@row - [Actual Date]@row > 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Early", IF([Planned Date]@row - [Actual Date]@row = 0, "0 Variance, On Schedule")))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Finish Date (Planned)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) < 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row)) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) > 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row)) + " Day(s) Early", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) = 0, "0 Variance, On Schedule")))))


    Give something like the above a go.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Very Close. It is including today. For example, Plan = Actual should have 0 Variance instead of 1 Day early as the variance


    =IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Finish Date (Planned)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) < 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row)) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) > 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row)) + " Day(s) Early", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) = 0, "0 Variance, On Schedule")))))

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Excellent! That worked. Thanks for All your help

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭
    Answer ✓

    Something is out of Order in the formula. Take a look at Finish Date (Planned)10/25/19 and Finish Date Actual 12/13/19. The result is 35 Day Early instead of 35 days late. I can share the sheet if you like?


    =IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Finish Date (Planned)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 < 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 > 0, ABS(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1) + " Day(s) Early", IF(NETWORKDAYS([Finish Date (Planned)]@row, [Finish Date (Actual)]@row) - 1 = 0, "0 Variance, On Schedule")))))

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Excellent, Prefect! Thanks for all your help now and in the future lol. I am always trying to create formulas to automate and streamline my PM Processes

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Good Day Paul:

    I found another Error in our Formula. For Day Ahead of Schedule it seems to be counting more days than the actual date. For example:


    Plan Date 10/11/19

    Actual Date 10/10/19


    The formula display results of 3 Days Early

    =IF(ISBLANK([Actual Date]@row), "", IF(AND(ISBLANK([Planned Date]@row), ISDATE([Actual Date]@row)), "Start Date Missing", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1 < 0, ABS(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1) + " Day(s) Early", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1 > 0, ABS(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1 = 0, "0 Variance, On Schedule")))))


    I was also testing this formula to use which resulted in the numeric value of -1 using the above dates, which is correct. I am thinking of maybe adding that to the above formula which may make simpler. Your thoughts?

    =IFERROR(DATEONLY([Actual Date]5) - [Planned Date]5, "")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF(ISBLANK([Actual Date]@row), "", IF(AND(ISBLANK([Planned Date]@row), ISDATE([Actual Date]@row)), "Start Date Missing", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) + 1 < 0, ABS(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) + 1) + " Day(s) Early", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1 > 0, ABS(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1) + " Day(s) Behind Schedule", IF(NETWORKDAYS([Planned Date]@row, [Actual Date]@row) - 1 = 0, "0 Variance, On Schedule")))))


    Check out the above. I think that was my fault. For "Early" we need to add a day instead of subtracting a day since we are actually working in negative numbers. I did some quick testing on it, and it seems to work. Let me know what you think.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Paul:

    I am trying to replace DATEOnly with Network Days so I only get Working Days. I thought it would be as simple as replacing he function, but I was unsuccessful. What am I doing wrong?


    =IFERROR(DATEONLY([Finish Date (Actual)]12) - [Finish Date (Plan)]12, "")

    =IFERROR(NETWORKDAYS([Finish Date (Actual)]12) - [Finish Date (Plan)]12, "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!