Excel: Finding last day of the month in a calendar week 2

[firegambler]

Hi Folks,

this is a tough one that makes my brain ache:

I have a list of calendar weeks and I have to find a formula that tells me whether the last day of the month is in this week so that the cell displays "YES" in case the week contains the last day of the month and "NO" if it doesn't.
E.g.:
week 25 26 27
NO YES NO
(contains first of july)


Is there a way to figure this out without using VB?


Thanks a ton


regards

 

[GlennUK]

Hi firegambler,

what day of the week are you using as start of week? Sunday or Monday? ( these are the 2 days most commonly used for start of week )

Also, you've asked for help identifying weeks that have the last day of the month, and then you show an example that has a comment saying

(contains first of july)

I have to point out the last day of one month, and the first day of the next might be in different weeks. So, which do you really want to search for?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AlaskanDad]

I've put together a quick solution for you:

Try this:

Cell A1: 1/1/2005 (this will be used to establish the beginning of the year)

Week #  Week start      Week end        EOM?
1       1/2/2005        1/8/2005        FALSE
2       1/9/2005        1/15/2005       FALSE
3       1/16/2005       1/22/2005       FALSE
4       1/23/2005       1/29/2005       FALSE
5       1/30/2005       2/5/2005        TRUE

Here are the formulas

Cell B3 (shown above as 1/2/2005): =IF(WEEKDAY($A$1,1)=1,$A$1,A1+(8-WEEKDAY($A$1,1)))
Note: the last 1 in the Weekday function indicates Sunday as the first day of the week. Change it to 2 if you want Monday to be the first day of the week
Cell C3 (shown above as 1/8/2005): =B3+6
Cell D3 (shown above as False): =(((B3-1)<DATEVALUE((MONTH(B3)+1)&"/1/"&YEAR(B3))-1) + ((C3+1)>DATEVALUE((MONTH(B3)+1)&"/1/"&YEAR(B3))-1))=2

Cell B4 (shown above as 1/9/2005): =C3+1
Cell C4 (shown above as 1/15/2005): =B4+6
Cell D4 (shown above as False): =(((B4-1)<DATEVALUE((MONTH(B4)+1)&"/1/"&YEAR(B4))-1) + ((C4+1)>DATEVALUE((MONTH(B4)+1)&"/1/"&YEAR(B4))-1))=2

Now, copy cells B4-D4 down to cover all of your weeks.

There's not an easy way to find the end of the month, but it's very easy to find the first of the next month and subtract 1.

Sure, it's ugly but it works.

Rob

 

[TonyJollans]

There's not an easy way to find the end of the month

There is! You need the Analysis ToolPak AddIn, but you can then use:
[blue][tt] =EOMONTH(B4,0)[/tt][/blue]

As for the original question, I would compare the month of the start of the week with the month of the start of the next week so with week start dates as in Rob's example in column B, in D2 put the formula:
[blue][tt] =MONTH(B2)<>MONTH(B3)[/tt][/blue]

Of course if you don't actually have the week start dates it will be a bit more awkward

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AlaskanDad]

Tony,

Thanks for the clarification! I wasn't aware of any of the functions in the Analysis TookPak.

Unfortunately, your month to month check falls apart on week 17 of this year:

17 4/24/2005 4/30/2005

That week does have the end of month in it but the two months are identical.

Rob

 

[TonyJollans]

Hi Rob,

My comparison is between the month of the start of week 17 (April) and the start of week 18 (May - if week 17 ends on the last day of April, week 18 starts on the first day of May).

Thinking about it after I posted it would be better just to use the start of the week instead of relying on the next week being there ..
[blue][tt] =MONTH(C2)<>MONTH(C2+7)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[firegambler]

@ Glenn: sorry for being confusing in my description

Alaskan & Tony,
thanks for putting so much thought in my problem,

alaskan you made my day!
There goes a star for you.
Thanks a lot!

regards

 

[AlaskanDad]

Tony,

You were right! I prefer your elegant solution over mine.

Rob