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

  • Thread starter Thread starter Jeff H
  • Start date Start date
J

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.
 
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")
 
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.
 
Something like this maybe?

=DATE(YEAR(A2), MONTH(A2)+1+(MONTH(WORKDAY(A2,10))<>MONTH(A2)),0)
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top