Name The Week Of The Month

[Mathew]

Good Afternoon All,



This is my second posting of this query. I did get some help, from Bob using

=INT(C3/7)+1 in Col A, (Thanks Bob), but it did not quite work for all
cases,

eg if $A$1 is 1st August 08, where the 1st is a Friday (week1), but the
following

Monday (the 4th) comes out as week 1 also, where it is supposed to be week
2.

Thanks to some help received here on my thread - Name The Date2 - I now need
to be able to name the Week in the month (ie 1-5) that the Day (Mon/Tues...)
falls in.
Example Spreadsheet below

A B C
1 = Today()
2 Week No Day Date (Header Row)
3 Tue 01
4 Wed 02
5 Thu 03
6 Fri 04



Etc Etc Etc


The Formula In Column B3 & copied down is
=TEXT(DATE(YEAR($A$1),MONTH($A$1),TRIM(C3)),"DDD")
which gives the Day of the date in Column C
Now in Column A, I would like a way to name the week of the month, ie 1 to
5. In this case the first Monday (the 7th) would be in Week2, but the
formulae I have tried, (including combinations of IF statements don't seem
to work, as only can nest 7), want to use the dates 01-07 as Week1, which is
obviously incorrect in this case.
I am trying to summarize all my company's direct debits, but have found it
difficult as the DAYS of the month fall on Different DATES of the month.
I hope I have explained this okay, and the formatting works when I post it!

As usual, any help is most appreciated.
Cheers
Mathew

 

[Ron Rosenfeld]

Good Afternoon All,



This is my second posting of this query. I did get some help, from Bob using

I am trying to summarize all my company's direct debits, but have found it
difficult as the DAYS of the month fall on Different DATES of the month.
I hope I have explained this okay, and the formatting works when I post it!

As usual, any help is most appreciated.
Cheers
Mathew

What, exactly, are you trying to do?

What rules do you want to use to determine what week of the month a particular
date is?
--ron

 

[Mathew P Bennett]

Hi Ron, Thanks for your interest to help.
It is all about the 'working week', (Mon-Fri). Column C is simply the Date
of the month (1-31), Column B is a formula (see post) that works out the
actual Day (Mon/Tue...) depending on the date in $A$1 (actually today's
date, =Today()).
Now the crux of the matter, in Column A, I would like a formula/function
that works out into which week (working) hat each date falls into. eg 1st of
August 2008 falls on a Friday, so as it is a working day, is actually week 1
of August. Now, the following Mon-Fri should be week 2 etc, and the final
Mon-Fri (29th) shows week 5.
Then, because it is all driven by $A$1, he whole thing changes on the 1st of
each month.

Knock up the ss, and see how it should work, I think the example is a good
representation of my ss.

Cheers Ron, I do hope you can help. As I said earlier, I have tried various
work-arounds, but still flummoxed!

Mathew

 

[Ron Rosenfeld]

Hi Ron, Thanks for your interest to help.
It is all about the 'working week', (Mon-Fri). Column C is simply the Date
of the month (1-31), Column B is a formula (see post) that works out the
actual Day (Mon/Tue...) depending on the date in $A$1 (actually today's
date, =Today()).
Now the crux of the matter, in Column A, I would like a formula/function
that works out into which week (working) hat each date falls into. eg 1st of
August 2008 falls on a Friday, so as it is a working day, is actually week 1
of August. Now, the following Mon-Fri should be week 2 etc, and the final
Mon-Fri (29th) shows week 5.
Then, because it is all driven by $A$1, he whole thing changes on the 1st of
each month.

Knock up the ss, and see how it should work, I think the example is a good
representation of my ss.

Cheers Ron, I do hope you can help. As I said earlier, I have tried various
work-arounds, but still flummoxed!

Mathew

Well, if your week always begins on Monday, then the following should return
what you are looking for.

A1: Base Date
A2: Week Number
B2: Day
B3: Date

A3: =IF(B3="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&B3)))=2))+1)
B3:
=IF(MONTH($A$1)=MONTH($A$1-DAY($A$1)+ROWS($1:1)),$A$1-DAY($A$1)+ROWS($1:1),"")
custom format as ddd

C3 =B3
custom format as dd

Select A3:B3 and fill down 31 rows.
--ron

 

[Ron Rosenfeld]

Well, if your week always begins on Monday, then the following should return
what you are looking for.

A1: Base Date
A2: Week Number
B2: Day
B3: Date

A3: =IF(B3="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&B3)))=2))+1)
B3:
=IF(MONTH($A$1)=MONTH($A$1-DAY($A$1)+ROWS($1:1)),$A$1-DAY($A$1)+ROWS($1:1),"")
custom format as ddd

C3 =B3
custom format as dd

Select A3:B3 and fill down 31 rows.
--ron

Oops.

The above will occasionally give incorrect results.

I wrote "week always begins on Monday".

That being the case, the formula for A3 should be:

=IF(B3="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1)+1&":"&B3)))=2))
+OR(WEEKDAY($A$1-DAY($A$1))={2,3,4,5}))

The only potential problem with this formula is that if the month begins on a
Saturday, then the first Saturday and Sunday will belong to week zero (0).

Would you rather have that, or have the week start on Saturday?
--ron

 

[Ron Rosenfeld]

Well, if your week always begins on Monday, then the following should return
what you are looking for.

A1: Base Date
A2: Week Number
B2: Day
B3: Date

A3: =IF(B3="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&B3)))=2))+1)
B3:
=IF(MONTH($A$1)=MONTH($A$1-DAY($A$1)+ROWS($1:1)),$A$1-DAY($A$1)+ROWS($1:1),"")
custom format as ddd

C3 =B3
custom format as dd

Select A3:B3 and fill down 31 rows.
--ron

OK, this formula in A3 will have the week starting on Saturday, if that's what
you prefer:

=IF(B3="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1)+1&":"&B3)))=7))
+(WEEKDAY($A$1-DAY($A$1))<>6))

--ron

 

[Mathew]

Giood morning Ron, Thank you very much, your solution is great, I would not
have been able to fathom out your formula. I have gone for the week
beginning Saturday.
Thanks again.
Mathew

 

[Ron Rosenfeld]

Giood morning Ron, Thank you very much, your solution is great, I would not
have been able to fathom out your formula. I have gone for the week
beginning Saturday.
Thanks again.
Mathew

Glad to help. Thanks for the feedback.

Let me know (preferably in this thread) if any problems arise, or if you have
any questions.
--ron