Nth day of month of year

[Michele]

Hi,
I'd like to be able to have a column which would tell me if a day i
the first, second, third, fourth or fifth of the month.

For example (European Date notation):

1/12/03 is the first monday of the month = 1
2/12/03 is the first tuesday of the month = 1
3/12/03 is the first wednesday of the month = 1
...
8/12/03 is the second monday of the month = 2
9/12/03 is the second tuesday of the month = 2
10/12/03 is the second wednesday of the month = 2
...
9/3/04 is the second tuesday of the month = 2...

On http://www.cpearson.com/excel/DateTimeWS.htm I found followin
formula which is almost what I'm looking for.
But I'm afraid I don't understand it well enough to transform it int
the formula I would need to calculate what I need above.
----------
Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for
given month and year. For example, it will return 26-March-98 for th
4th Thursday of March, 1998. Days-of-week range from 1 to 7, wit
Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicatin
Year, Month, Nth, and Day-Of-Week

 

[Guest]

So what you're after is really the week of the month - this should do it for you, assuming dates in col A
Enter the formula into B1 and copy down
=INT((A1-VALUE("01/" & MONTH(A1)&"/"&YEAR(A1)))/7)+1

 

[Michele]

Thanks.

 

[Daniel.M]

Hi Michele,

=MATCH(DAY(A1),{1;8;15;22;29})

Regards,

Daniel M.

Hi,
I'd like to be able to have a column which would tell me if a day is
the first, second, third, fourth or fifth of the month.

For example (European Date notation):

1/12/03 is the first monday of the month = 1
2/12/03 is the first tuesday of the month = 1
3/12/03 is the first wednesday of the month = 1
..
8/12/03 is the second monday of the month = 2
9/12/03 is the second tuesday of the month = 2
10/12/03 is the second wednesday of the month = 2
..
9/3/04 is the second tuesday of the month = 2...

On http://www.cpearson.com/excel/DateTimeWS.htm I found following
formula which is almost what I'm looking for.
But I'm afraid I don't understand it well enough to transform it into
the formula I would need to calculate what I need above.
----------
Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a
given month and year. For example, it will return 26-March-98 for the
4th Thursday of March, 1998. Days-of-week range from 1 to 7, with
Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating
Year, Month, Nth, and Day-Of-Week
-------

Thank you for reading and eventually answering this question.

Michèle


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to creating

financial statements