Calculating 10 Business Days from the Last Business Day of the Mon

[Jeff H]

OK, I am drawing a complete blank on this one. I'm having a total brain
glitch. Is this a sign that maybe I need to step away for a minute? (Maybe.)

I need to determine if a particular date is greater than 10 business days
from the last business day of the month.

 

[Luke M]

Assuming you meant, if a date is within 10 days from end of month, something
like this:

=IF(MONTH(WORKDAY(A2,10))=MONTH(A2),"Still have time","You're within 10 days")

 

[Jeff H]

Thanks, Luke. Actually, what I looking at is...
If Date > 10 business days from the last business day of the month
Then Close Date = End of Month
Else Close Date = End of (Month+1)

I'm pretty sure that I have the End of Month calculations down right.
End of Month = Date(Year(Date), Month(Date)+1,0)
End of (Month+1) = Date(Year(Date),Month(Date)+2,0)

For example, the last business day of this month (July) is 31st. So, Close
Date for July 16 would be July 31. However, Close Date for July 20 would be
August 31.

Hope that makes better sense.

 

[Rick Rothstein]

Something like this maybe?

=DATE(YEAR(A2), MONTH(A2)+1+(MONTH(WORKDAY(A2,10))<>MONTH(A2)),0)

 

[Ron Rosenfeld]

OK, I am drawing a complete blank on this one. I'm having a total brain
glitch. Is this a sign that maybe I need to step away for a minute? (Maybe.)

I need to determine if a particular date is greater than 10 business days
from the last business day of the month.

If your particular date is in A1, and if the last business day of the month
also refers to the same month as the date in A1, then:

=IF(NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,0))>10,
"More","Less")&" ten (10) business days"
--ron

 

[Ron Rosenfeld]

Thanks, Luke. Actually, what I looking at is...
If Date > 10 business days from the last business day of the month
Then Close Date = End of Month
Else Close Date = End of (Month+1)

I'm pretty sure that I have the End of Month calculations down right.
End of Month = Date(Year(Date), Month(Date)+1,0)
End of (Month+1) = Date(Year(Date),Month(Date)+2,0)

For example, the last business day of this month (July) is 31st. So, Close
Date for July 16 would be July 31. However, Close Date for July 20 would be
August 31.

One formula for that would be:

=IF(NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,0))>10,
DATE(YEAR(A1),MONTH(A1)+1,0),DATE(YEAR(A1),MONTH(A1)+2,0))

A little shorter version:

=DATE(YEAR(A1),MONTH(A1)+2-(NETWORKDAYS(
A1,DATE(YEAR(A1),MONTH(A1)+1,0))>10),0)

--ron