Week Day Week of Month

[Drew]

b2's post seems to be getting cut off.

=DATE(YEAR(A2),MONTH(A2),1)+IF($A$1


[ This Message was edited by: Drew on 2002-05-07 10:57 ]

[ This Message was edited by: Drew on 2002-05-07 11:55 ]

[IML]

one more for fun

=INT(DAY(A1)/7)+1&CHOOSE(INT(DAY(A1)/7)+1,"st","nd","rd","th","th")&TEXT(A1," dddd")

good luck

EDIT - I suppose fun should somehow equate to working, how about a revised formula of
=INT(DAY(A1)/7.5)+1&CHOOSE(INT(DAY(A1)/7.5)+1,"st","nd","rd","th","th")&TEXT(A1," dddd")


FINAL EDIT ? to you format
=INT(DAY(A1)/7.5)+1&"-"&WEEKDAY(A1,2)
[ This Message was edited by: IML on 2002-05-07 10:49 ]

[ This Message was edited by: iml on 2002-05-07 11:00 ]

[JasonJ]

Not 100% sure of the needs here, but the first formula doesn't quite work. I have a shorter, and easier to understand method for finding "Week of the Month"

The problem comes in when a month STARTS mid-week. Lets say the month STARTS on Friday, so Monday is the 4th. Monday should be in the 2nd week.

The solution is you need to know the week of the TARGET date AND the week of the FIRST of the month. Using these two weeks, we can "normalize" to something like the following:

=WEEKNUM(B2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1))+1

B2 is the cell with the date in question.

The first weeknum gets the week (of the year) of the target date. The second gets the week (of the year) of the 1st of the current month. You subtract and then add 1, so the first week will be 1 not zero.

This is how you can get the week of the month from a given date.

Now I was confused on if you were trying to convert BACKWARDS (as above) or have it calculate the "next" date.

If you are trying to find the "next" date then you must add either 28 or 35 (4 weeks or 5) to your current date, and make sure it is in fact the next month:

=IF(MONTH(B2)=MONTH(B2+28),B2+35,B2+28)

This checks to see if the target date+28 is the same month, if so, then add 35, otherwise, add 28.

Hope this helps.

[JasonJ]

Not 100% sure of the needs here, but the first formula doesn't quite work. I have a shorter, and easier to understand method for finding "Week of the Month"

The problem comes in when a month STARTS mid-week. Lets say the month STARTS on Friday, so Monday is the 4th. Monday should be in the 2nd week.

The solution is you need to know the week of the TARGET date AND the week of the FIRST of the month. Using these two weeks, we can "normalize" to something like the following:

=WEEKNUM(B2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1))+1

B2 is the cell with the date in question.

The first weeknum gets the week (of the year) of the target date. The second gets the week (of the year) of the 1st of the current month. You subtract and then add 1, so the first week will be 1 not zero.

This is how you can get the week of the month from a given date.

Now I was confused on if you were trying to convert BACKWARDS (as above) or have it calculate the "next" date.

If you are trying to find the "next" date then you must add either 28 or 35 (4 weeks or 5) to your current date, and make sure it is in fact the next month:

=IF(MONTH(B2)=MONTH(B2+28),B2+35,B2+28)

This checks to see if the target date+28 is the same month, if so, then add 35, otherwise, add 28.

Hope this helps.