Troubleshooting Assistance: Working Days in Month Based on Date Range

[KBChristensen]

Hi everyone,

I'm trying to troubleshoot some issues I'm having with a formula for finding working days in each month in a given date range.

In the example above you can see the formula I'm trying to apply to my "real" sheet. I have no trouble getting it to work in this "test" sheet, but as you can see below, it fails to work properly in the "real" sheet.

For Row 2 I would've expected the result to be: J2 = 12, K2 = 2, L2 = 0, M2 = 0 and so on...

For Row 3 I would've expect the result to be: J3 = 10, K3 = 0, L3 = 0, M3 = 0 and so on...

Anyone able to discern or have an idea what I'm doing wrong in the "real" sheet?

 

[Fluff]

Hi & welcome to MrExcel.
Is you "real" sheet formatted as a structured table?
Also can you please post the actual formula, rather than an image.

 

[KBChristensen]

Hi Fluff,

Thanks for responding.

I think it is, yes. It is a combination of two other sheets.

The formula is here:
=MAX(0,NETWORKDAYS.INTL(MAX(J$1,$C2),MIN(EOMONTH(J$1,0),$D2),1))

 

[Fluff]

I think it is, yes

That's the problem, tables convert the headers to text, so they are not real dates.
Try

=MAX(0,NETWORKDAYS.INTL(MAX(--J$1,$C2),MIN(EOMONTH(--J$1,0),$D2),1))

 

[KBChristensen]

That did the trick, Fluff. Thanks a lot for the help.

 

[Fluff]

You're welcome & thanks for the feedback.