Project Task NETWORKDAYS (standard calendar days) spanning different accounting calendar days

youngrc

My organization's accounting calendar varies from a standard monthly calendar as shown below. I am trying to determine a method of calculating the number of NETWORKDAYS within a project task span that falls within each month's corresponding accounting calendar span. It is simple enough if the entire project task falls between the accounting month dates. However, I cannot find a way to calculate the number of NETWORKDAYS when the project span overlaps and extends into different accounting months. See the table below. Non-working days are weekends and Independence Day (7/4/22) in the example below. I have manually calculated the number of NETWORKDAYS and filled in these days in the highlighted green areas. The number of NETWORKDAYS is 15, with 12 falling within the July accounting period of 6/27/22 - 7/24/22 and 3 within the August accounting period of 7/25/22 - 8/28/22.

I need to determine the correct formula(s) to automatically calculate and populate the denoted monthly cells with the number of NETWORKDAYS for the project task within the proper accounting month, including a value of 0 when applicable (0 for June in this case).

youngrc

Anyone out there got any ideas?

Mike TV

@youngrc

First, I need to understand your method of counting NETWORKDAYS. For example, your Project starts on 07/07 and ends on 07/27. You say weekends aren't counted, so looking at the calendar I'm manually counting 15 work days between those dates. Why did your manual count come up with only 12? If you're not wanting to include the 7th or the 27th you'd still get 13.

youngrc

First of all, thank you for the feedback. It is appreciated. The example project task of 7/7/2022 to 7/27/22 is 15 workdays (Thursday 7/7 and Friday 7/8, Monday 7/11 through Friday 7/15, Monday 7/18 through Friday 7/22, then Monday 7/25 through Wednesday 7/27. This totals to 15 workdays.

Now reference the accounting calendar for July and August in my previous message (6/27 through 7/24 for July and 7/25 through 8/28 for August. Out of the 15 workdays described above, 12 fall within the July calendar span (working days between 7/7 and 7/24) and 3 within the August calendar span (7/25 through 7/27).

I have tried to find a way to automatically calculate and populate each accounting calendar month cell as shown above with the correct working days based on the project task span dates. I'd like to be able to do this for forecasting purposes when tasks happen to be adjusted during project activity. Sometimes tasks may be reduced or extended in time span, or just shifted by moving up or pushing out scheduled activity. When this occurs, I'd like to be able to know where the workdays are falling within the accounting calendar.

Thanks again for the interest.

Julio S.

Hi @youngrc,

To accomplish this, I would suggest creating sheet summary fields with the definition of each Accounting Month start and finish to easily reference each month in each of the formulas you will need for each of them. Then you could use formulas in the line of the following for each month (June in this instance):

=MAX(NETWORKDAYS(MAX(MIN(Start@row, [Accounting Finish June]#), [Accounting Start June]#), MIN(Finish@row, [Accounting Finish June]#))

I hope this can be of help.

Cheers!

Julio

Andrée Starå

Hi @Julio S.

I hope you're well and safe!

I've noticed that I usually can't see your screenshots. Probably because of security in Salesforce.

This is how it looks to me.

And if I click on the link, this is where it takes me.

https://smartsheet.okta.com/app/salesforce/exk5h1gecw89F0K3u1t7/sso/saml

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

youngrc

Julio,

I will give this a try as soon as I get a chance.

Thank you,

Rick

Julio S.

Thanks for pointing this out, @Andrée Starå . I hope you are also well. This should've been amended now. Please let me know if you still can't see the image.

Andrée Starå

@Julio S.

Happy to help!

Yes, now I can see the image.

youngrc

Hi Julio,

I implemented per your instructions and everything worked as it should up to a point. I went ahead and setup accounting calendar start/finish dates in the sheet summary for all of 2022 and 2023. Regarding this particular example task activity between 7/7/22 and 7/27/22, all accounting calendar data through June populated my columns with "0", as expected, then "12" and "3" for July and August, as expected.

However, after August, I am seeing negative values (-24 and -43 for September and October, respectively).

The Accounting calendar dates for September and October are:

Month Start Finish

Sept 8/29/22 9/25/22

Oct 9/26/22 10/30/22

I would expect to see "0" values for everything prior to July and after August. As mentioned above, I did get the "0" values through June, but the negative values starting in September. I am not certain where the issue lies.

Is there something that needs to be adjusted for MIN and MAX in the formula?

Thanks,

Rick

youngrc

I went ahead and pasted in the results for Jan 22 through Jan 23. As I mentioned above, everything up through Aug 22 comes through accurately, but anything after Aug 22 calculates the negative number results shown below.

Again, the project task is 7/7/22 through 7/27/22; 15 NETWORKDAYS.

Thanks,

Rick

Cary S.

Hello Rick!

It looks like that issue can be solved by including a ,0 inside of the first MAX function: =MAX(NETWORKDAYS(MAX(MIN(Start@row, [Accounting Finish June]#), [Accounting Start June]#), MIN(End@row, [Accounting Finish June]#)), 0)

I hope this helps!

Cary

youngrc

Thanks Cary. Appreciated.

Rick