Formula to Subtract a Given Date from the Beginning of the Month of Same Date

[clericalguy]

Hi

I would like to obtain the number of working days from the beginning of the month of a given date up to and including the same date.

Example:

29/01/2024 = 21 days

Thanks.

 

[Joe4]

For a date in cell A1, use this formula to get your desired value:

=NETWORKDAYS(EOMONTH(A1,-1),A1)

 

[clericalguy]

For a date in cell A1, use this formula to get your desired value:

=NETWORKDAYS(EOMONTH(A1,-1),A1)

Thanks, the formula works on its own. But when I try to use it with IF statement I get FASE.

=IF(D21="New Hire",NETWORKDAYS(D22,EOMONTH(D22,0),(NETWORKDAYS(EOMONTH(D22,-1),D22))))

So if D21 is anything else, the second part of the statement returns FALSE.

 

[Joe4]

When that happens, it usually is an indication that you have structured your IF formula wrong, and you have no FALSE argument, in which case Excel will default to the word "FALSE".
The issue is that you have your closing parentheses in the wrong place. You need to close off/complete the first NETWORKDAYS calculation before you jump into the second one.

Try this:

=IF(D21="New Hire",NETWORKDAYS(D22,EOMONTH(D22,0)),NETWORKDAYS(EOMONTH(D22,-1),D22))

 

[clericalguy]

When that happens, it usually is an indication that you have structured your IF formula wrong, and you have no FALSE argument, in which case Excel will default to the word "FALSE".
The issue is that you have your closing parentheses in the wrong place. You need to close off/complete the first NETWORKDAYS calculation before you jump into the second one.

Try this:

=IF(D21="New Hire",NETWORKDAYS(D22,EOMONTH(D22,0)),NETWORKDAYS(EOMONTH(D22,-1),D22))

Beautifully done! Thank you!

 

[Joe4]

You are welcome.
Please note: When marking a post as the solution, please mark the post containing the solution, and not your own post acknowledging that some other post is the solution.
I have updated this thread for you.

 

[clericalguy]

What is the formula to count number of working days between beginning of month up to the day before a specific date?
And number of working days from specific date to end of month?

 

[Joe4]

What is the formula to count number of working days between beginning of month up to the day before a specific date?

How is that different than your original request?
Is it that you original request was "up to and including the specific date" and the new request is the day "BEFORE the specific date"?
If so, just subtract one from the date in the calculation, i.e.
change "D22" to "D22-1" in the parts where you want to make that change.

And number of working days from specific date to end of month?

Note the structure of the NETWORKDAYS formula (from: NETWORKDAYS function - Microsoft Support):
NETWORKDAYS(start_date, end_date, [holidays])

So just order the values accordingly, realizing that:
EOMONTH(date, 0)
returns the end of the month date for whatever date you put in the "date" part of the formula, and
EOMONTH(date, -1)
returns the end of the month date for the previous month's date (for the date in the "date" part of the formula)