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
-
That's because having start/finish on the same day is considered 1 full workday. NETWORKDAYS is more of a "duration" than a "variance". Try subtracting 1 from the NETWORKDAYS functions to account for one day durations.
=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")))))
-
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")))))
-
Lets try just switching up the verbiage a touch...
=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) Early", 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, "0 Variance, On Schedule")))))
-
If both dates are the same, it should have given you a 1. Since you are starting and finishing on the same day, you are taking up 1 day. That's why if it seems like the dates are only one day apart (today and tomorrow), you would get a two.
Does that make sense/help any?
Answers
-
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
-
Yes, I tried, but I would get an Error. I am not sure where to place it in my formula
-
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.
-
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")))))
-
=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.
-
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")))))
-
That's because having start/finish on the same day is considered 1 full workday. NETWORKDAYS is more of a "duration" than a "variance". Try subtracting 1 from the NETWORKDAYS functions to account for one day durations.
=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")))))
-
Excellent! That worked. Thanks for All your help
-
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")))))
-
Lets try just switching up the verbiage a touch...
=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) Early", 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, "0 Variance, On Schedule")))))
-
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
-
-
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, "")
-
=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.
-
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
Categories
Check out the Formula Handbook template!
