Last day of month that isn't saturday or sunday

B

Brent

Is there a quick formula to look at a date and calculate the last day of
that month that isn't a Saturday or Sunday. Date field is InvDate.

Thanks
Brent
 
M

[MVP] S.Clark

Last Day of month is easy. Take the first day of the next month, then
subtract one day. Use DateSerial() to help.
Once that day is known, get the DOW for that day. Use that to determine if
it is a Sat or Sun.
 
M

Marshall Barton

Brent said:
Is there a quick formula to look at a date and calculate the last day of
that month that isn't a Saturday or Sunday. Date field is InvDate.
Click to expand...


Try something like:

DateSerial(Year(somedate), Month(somedate) + 1. 0) -
IIf(Weekday(DateSerial(Year(somedate), Month(somedate) + 1.
0), 7) > 2, 0, (Weekday(DateSerial(Year(somedate),
Month(somedate) + 1. 0), 7))
 
B

Brent

Thanks
Marshall Barton said:
Try something like:

DateSerial(Year(somedate), Month(somedate) + 1. 0) -
IIf(Weekday(DateSerial(Year(somedate), Month(somedate) + 1.
0), 7) > 2, 0, (Weekday(DateSerial(Year(somedate),
Month(somedate) + 1. 0), 7))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

function for date of first and last day through weeknumber 1
Count Weeks in a Month 7
workingdays in Acess 2
How to write a query that add 1 working day to the date 2
Calculate a future date 2
Get first Sun last Sat 3
Determining the Sunday date of the third "full" weekend of a month 25
Weekend Date 1

Top