Week Day Week of Month

Gary Kaye

New Member
Joined
May 3, 2002
Messages
11
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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
 
Upvote 0

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
 
Upvote 0
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......
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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<WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2),7-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2)+$A$1,$A$1-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2))+(($B$1-1)*7)
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top