Calendar Formula 1st Day of Month, 2nd Wed, Last Day of Month

**Wskip49** · 06-30-2012, 10:30 AM

I am trying to set up a spread sheet with the 1st Week Day of Month, 2Nd Wed of the Month and Last Week Day of Month. What or is there a formula that will give me those dates each month?

**royUK** · 06-30-2012, 10:41 AM

Which version of Excel are you using?

**Ron Coderre** · 06-30-2012, 10:42 AM

With
A1: any date....15-Jun-2012

Try these regular formulas:
First of the month:
=A1-DAY(A1)+1

Last of the month:
=EOMONTH(A1,0)....Excel 2007+ or Pre-2007 with Analysis ToolPak
=DATE(YEAR(A1),MONTH(A1)+1,0)...All versions

2nd Wednesday of the month:
=(A1-DAY(A1)+1+7*2)-WEEKDAY(A1-DAY(A1)+8-MATCH("Wed",{"SUN","MON","TUE","WED","THU","FRI","SAT"},0))
or...since you know you want Wednesday:
=(A1-DAY(A1)+1+7*2)-WEEKDAY(A1-DAY(A1)+4)

Is that something you can work with?

**Wskip49** · 06-30-2012, 10:45 AM

I am using 2007

**Wskip49** · 06-30-2012, 01:38 PM

This may be pushing it a little, however, I would like the formula to tell me the first week day of the month, and the last week day of the month. No action takes place on weekends.

Thanks

**Ron Coderre** · 06-30-2012, 03:19 PM

Originally Posted by Wskip49

This may be pushing it a little, however, I would like the formula to tell me the first week day of the month, and the last week day of the month. No action takes place on weekends.

Thanks

Not a problem...especially since you're using Excel 2007
First workday of the month:
=WORKDAY(EOMONTH(A1,-1),1)

Last workday of the month:
=WORKDAY(EOMONTH(A1,0)+1,-1)

NOTE: the WORKDAY function can read from a list of holiday dates and exclude them from "workdays".
So, if cells Z1:Z12 contained holiday dates, these would be the formulas:
First workday of the month:
=WORKDAY(EOMONTH(A1,-1),1,Z1:Z12)

Last workday of the month:
=WORKDAY(EOMONTH(A1,0)+1,-1,Z1:Z12)

(See Excel Help for the details.)

Does that help?