Variance Between 2 Dates (Weekdays Only)

Anthony Barthelemy

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")))))

Paul Newcome

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")))))

Anthony Barthelemy

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")))))

Paul Newcome

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")))))

Paul Newcome

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?

Alejandra

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

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

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

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

=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

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")))))

Paul Newcome

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")))))

Anthony Barthelemy

Excellent! That worked. Thanks for All your help

Anthony Barthelemy

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")))))

Paul Newcome

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")))))

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

Paul Newcome

Happy to help! 👍️

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

=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

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, "")