NETWORKDAYS.INTL() Workaround?

1

Hi All,

I have been racking my brain for a simple way to accomplish something I do in Google Sheets for a while now and just have not come up with a solid idea that doesn’t involve reinventing the wheel.

I have a report I run daily comparing MTD sales in the current year to the prior year and estimate where sales might be based on the average daily sales. To do this I take the total MTD sales divided by the number of business days so far and then multiply the simple average times the total number of days in the month. (I know this is not a sophisticated projection but it tends to get the job done)

To accomplish this in Google Sheets I have a very automated sub-table on my report:
image

The only values I change are Start and End at the beginning of each month. The rest are based on formulas.

I can accomplish most of the formulas using Coda:
Today uses =today()
Report Day is =today()-Days(1)
Days Remaining =Total_Business_Days - Days_Down

What I am struggling with is how to accomplish the calculation for Total Business Days and Days Down because I use a specific formula in Google Sheets that Coda does not seem to have:
image

Formula Reference

image
image785×708 67.5 KB

Because different store locations can be open different days of the week (some closed Sunday, some open all week, others only Monday-Friday) this equation helps tremendously.

Does anyone have any alternatives to this that might work on Coda? :sweat_smile:

2

Hi @James_Eades,

As you correctly pointed out, there is not such specific flexible formula in Coda, for the moment.
If you think it could be somehow a useful workaround, have a look at this example:

I provided some intermediate columns just to show the output, but you basically can just keep the formula in Working Days column.
As well as - obviously - the Weekdays Pattern table (with the same logic as in Google function)

As said, it’s a workaround, but flexible enough to be quite portable in different tables.
Let me know if this helps.

Cheers!

3

This is great!

I do think there is a bit of an issue with the Solar Days formula.

thisRow.End-thisRow.Start returned 1 less day than expected

Sequence(thisRow.Start,thisRow.End,1 ).Count() fixed it for me.

1 Like
4

Yes, you are right: I corrected it.
This was a duration, so it’s a distance: it’s not counting all the instances.
I just added 1 :smiley:

Happy it was useful