Formula for the DATE of every Monday in 2003

  • Thread starter Thread starter bitsnpieces
  • Start date Start date
B

bitsnpieces

I am setting up a header row in a vacation schedule. It will look
something like this (for Jan and Feb 2003)

|
X 6 13 20 27 | X 3 10 17 24
|

Each of the above signifies the date of the Mondays occurring in
January and February of this year. I entered them manually. The |
bar shows the split between months and I have allotted five weeks for
each month, hence the X when there is no Monday for that particular
calendar week.

Is there a formula I can use, say at the beginning of the row, which
will automatically calculate these dates for me along the entire line
for the entire year?

I've seen and used one before but I don't recall what the formula is.
I think it was set up to calculate every seven days but I don't know
the part of the formula that handled the number of days in each month.
 
Each day has a value of 1. As long as you know the date of the first
Monday, and enter that in the first column then the formula for B1 is
(assuming your titles are in row 1) A1+7. Copy across the page

To format to only show the day value, highlight the row and
Format/Cell/Custom and enter dd to show the 3rd of the month as 03 or d to
show the 3rd day of the month as 3.

HTH

PC
 
Hi bitsnpieces

To do what you are now doing:

A1
X
B1
6-Jan-2003
C1
=IF(B1="X",A1+7,IF(MONTH(B1+7)=MONTH(B1),B1+7,"X"))

Copy across.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Wednesday: Curacao (Flag Day), Tibet (Chungbacixi
Festival)
Celebrations Wednesday: Chungbacixi Festival: Buddhism
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
bitsnpieces said:
I am setting up a header row in a vacation schedule. It will look
something like this (for Jan and Feb 2003)

|
X 6 13 20 27 | X 3 10 17 24
|

Each of the above signifies the date of the Mondays occurring in
January and February of this year. I entered them manually. The |
bar shows the split between months and I have allotted five weeks for
each month, hence the X when there is no Monday for that particular
calendar week.

Is there a formula I can use, say at the beginning of the row, which
will automatically calculate these dates for me along the entire line
for the entire year?

I've seen and used one before but I don't recall what the formula is.
I think it was set up to calculate every seven days but I don't know
the part of the formula that handled the number of days in each
Click to expand...
month.
 
Hi,

If you want to have constant 5 columns per month and put an 'X' where no
such 5th Monday exists,

In A1: 2003 ' the year
In B1: =DATE($A1,1,8)-WEEKDAY(DATE($A1,1,6)) ' first Mon of year A1
In C1:
=IF(MOD(COLUMN(),5)<>1,IF(B1="X",A1,B1)+7,IF(DAY(B1+7)<DAY(B1),"X",B1+7))

Copy C1 to BI1

Formula in C1 is dependent on the location, you'll have to change number 1
in MOD(COLUMN(),5)<>1 if you change the location (column wise).

Regards,

Daniel M.
 
Back
Top