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.
 
b2's post seems to be getting cut off.

=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 message was edited by Drew on 2002-05-07 10:57
This message was edited by Drew on 2002-05-07 11:55
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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