Thanks:  0
Likes:  0

# Thread: Week Day Week of Month

1. My company schedules services to customers based upon a fixed week and
weekday of each month, i.e.:

"1st Monday" of each month or "3rd Friday" of each month, and so on.

Every schedule excludes Sundays, but otherwise can be anywhere from 1-1
(1st Monday) through 4-6 (fourth Saturday).

If all I have is the last calendar DATE of the last service, how can I
write an excel formula to extract which week of month and day of the
week the service was actually performed? Monday must equal "1" and
Saturday must equal "6'. For all intent and purposes, we 'ignore' 5th
week-days of the month (they are not used for fixed appointments).

I've looked and tried to use all examples posted at other sites and your
website but
cannot find a series that works. I am not opposed to breaking the
action into 'steps' - just need a solution that works.

2. On 2002-05-04 03:27, Gary Kaye wrote:
My company schedules services to customers based upon a fixed week and
weekday of each month, i.e.:

"1st Monday" of each month or "3rd Friday" of each month, and so on.

Every schedule excludes Sundays, but otherwise can be anywhere from 1-1
(1st Monday) through 4-6 (fourth Saturday).

If all I have is the last calendar DATE of the last service, how can I
write an excel formula to extract which week of month and day of the
week the service was actually performed? Monday must equal "1" and
Saturday must equal "6'. For all intent and purposes, we 'ignore' 5th
week-days of the month (they are not used for fixed appointments).

I've looked and tried to use all examples posted at other sites and your
website but
cannot find a series that works. I am not opposed to breaking the
action into 'steps' - just need a solution that works.
Not sure I understand what you're asking, but

=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))

would give you 'week of month'.

=WEEKDAY(A1)

would give you the day of the week as number and

=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

would give you the day of the week as name [ Thanks to Ivan ].

[ This Message was edited by: aladin akyurek on 2002-05-04 04:41 ]

[ This Message was edited by: aladin akyurek on 2002-05-04 05:15 ]

3. On 2002-05-04 04:28, Aladin Akyurek wrote:

Not sure I understand what you're asking, but

=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))

would give you 'week of month'.

=WEEKDAY(A1)

would give you the day of the week as number and

=CHOOSE(WEEKDAY(A1),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

would give you the day of the week as name.

[ This Message was edited by: aladin akyurek on 2002-05-04 04:41 ]
Also Note: You need the Analysis ToolPac
Addin for the WeekNum function.

Plus =WEEKDAY(A1,2) would give you 6 for Sat

I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
say you need 6

4. Also Note: You need the Analysis ToolPac
Addin for the WeekNum function.

Plus =WEEKDAY(A1,2) would give you 6 for Sat

I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
say you need 6

Yes. Thanks for pointing out that. I edited the CHOOSE list, rather than adding a specifier to WEEKNUM itself.

What about the longer formula? That's the one I'm most worried about.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-04 05:20 ]

5. On 2002-05-04 05:19, Aladin Akyurek wrote:

Also Note: You need the Analysis ToolPac
Addin for the WeekNum function.

Plus =WEEKDAY(A1,2) would give you 6 for Sat

I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
say you need 6

Yes. Thanks for pointing out that. I edited the CHOOSE list, rather than adding a specifier to WEEKNUM itself.

What about the longer formula? That's the one I'm most worried about.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-04 05:20 ]
Yes your right, I guess you can always just
ref the cell =A1 and just format it as "ddd"
date format......

6. Regarding the problem "Week of Month and Week of Day" posted earlier...

Does not work as I need.

Formula suggested fails on this basis alone:

If the 4th or 5th week of any month ends, lets say on a "Wednesday" the formula may show it expressed correctly as "5-3" for 5th Wednesday. However the next day, Thursday is in the 1st week of June, but will be expressed as "5-4" rather than "1-4" for 1st Thursday as I would expect the 1st Thursday of the month to read...

Whatever the solution, each month can only have a maximum of 5 weeks and any one day of the week must be associated with the proper week of the month. Sounds simple, but...

Hope this problem doesn't fry anyone's brain as it has mine...

Gary.

7. On 2002-05-05 08:15, Gary Kaye wrote:
Regarding the problem "Week of Month and Week of Day" posted earlier...

Does not work as I need.

Formula suggested fails on this basis alone:

If the 4th or 5th week of any month ends, lets say on a "Wednesday" the formula may show it expressed correctly as "5-3" for 5th Wednesday. However the next day, Thursday is in the 1st week of June, but will be expressed as "5-4" rather than "1-4" for 1st Thursday as I would expect the 1st Thursday of the month to read...

Whatever the solution, each month can only have a maximum of 5 weeks and any one day of the week must be associated with the proper week of the month. Sounds simple, but...

Hope this problem doesn't fry anyone's brain as it has mine...

Gary.
American and European week numbering differ, a fact that makes difficult for me to check the correctness by comparing what my American Excel grinds out with what my European "office agenda" says. So I'd like to know which formula meets your need if any:

1) =IF(MONTH(A1)=1,WEEKNUM(A1),(WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))+1)

2) =IF(MONTH(A1)=1,WEEKNUM(A1),IF(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)),1,WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+1))

3) =IF(MONTH(A1)=1,WEEKNUM(A1),(WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))))+(ROUND((EOMONTH(A1,0)-A1+1)/7,0)=5)

4) =IF(MONTH(A1)=1,WEEKNUM(A1),WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))))

Maybe none of the above.

Regards,

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-05 09:01 ]

8. Thanks Aladin...but

Your 3rd and 4th suggested formulas would not even get past the 'Value' error and I could not fix the cause. 1 and 2 did but still returned errors in identifying which week of what month I expect. Looks like I'll have to manually identify over 5,000 records and dates to determine whether they express a 1st, 2nd, 3rd or 4th week of any given month correctly. The Day of Week has not been an issue at all. Thanks anyway. I'll watch for any further suggestions.

9. This may/may not be what you're looking for??
A majority of the function was taken from John Walkenbach (although I added to the function to meet your needs).
In a1 put the day number (1=Mon...7=Sun)
In b1 put the occurrence (if you wanted the 2nd week of the month put 2 in the cell)
In a2 put a date
In b2 put =DATE(YEAR(A2),MONTH(A2),1)+IF(\$A\$1 this will extract the target date and finally put in c2 =WEEKNUM(B2,2)+1-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)&"-"&\$A\$1 this will give you the day# and week# combo.
HTH,
Drew

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

[ This Message was edited by: Drew on 2002-05-07 13:25 ]

10. On 2002-05-07 02:16, Gary Kaye wrote:
Thanks Aladin...but

Your 3rd and 4th suggested formulas would not even get past the 'Value' error and I could not fix the cause. 1 and 2 did but still returned errors in identifying which week of what month I expect. Looks like I'll have to manually identify over 5,000 records and dates to determine whether they express a 1st, 2nd, 3rd or 4th week of any given month correctly. The Day of Week has not been an issue at all. Thanks anyway. I'll watch for any further suggestions.
Gary,

Your mail address in your profile is dydsfunctional. If you like, try mine:

aladin_akyurek@yahoo.com

Aladin

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•