Formula to return End OF Month date with non-calendar month lengths.

Hi gurus!
I have run into a problem that i feel should be easier to solve than it seems.

Scenario: I have a spreadsheet that is using a given date to show numbers from that date by referencing a day-by-day report. Most values can be gotten by using VLOOKUP but there is a problem.
One value i need to get returned from the report is the End Of Month date based on the given date (i.e. if i enter 2014-04-25 it should give me 2014-04-30). This works fine by just using the EOMONTH-function in Excel.
However, for a specific data-report, each month is not listed as the calendar month but rather a custom length where each months always have 7-day weeks.

In the attached example file i have noted that on "Sheet1", in cell B5 i enter a date and based on that date cell B2 should return the End Of Month date.
On "Sheet2" i have listed an example of two months and how they are listed in the data-report that is to be used to return the date in B2. As you can see, months end on different dates so EOMONTH cannot be used.

I then tried using HLOOKUP in this kind of formula: =HLOOKUP("Month"&" "&TEXT(I3;"[$-409]MMMM");'Sheet2'!$A:$B;9;FALSE). The lookup works so that it finds the cell called "Month XXXX" but then i need to get the formula to step upwards and return the first date it finds. Here is where i can't figure out how to do it since it isn't always the first cell above the cell "Month XXXX", it will be either the First or the Second one.

My thought is, can the "row_index_num" part of HLOOKUP be entered in the form of a formula or must it always be a number? Or do you have something completely differnt to suggest?

I hope you guys can help!

Originally Posted by :) Sixthsense :)

Please confirm the expected result for the given dates for testing purpose.

03/05/2014
07/05/2014
13/05/2014
25/05/2014

Hi!

03/05/2014
07/05/2014
13/05/2014
These should all return the date 23/05/2014 as according to Sheet2 calendar they are part of the Month of May.


25/05/2014
This should return the date 27/06/2014 as according to Sheet2 calendar they are part of the Month of June.

Originally Posted by :) Sixthsense :)

It will be very easy to pick the results (Month Name) if it stored in the next column (column-B) )

Hi,
For sure, but no, that isn't an option.

Pl see attached file.

Originally Posted by kvsrinivasamurthy

Try formatting the cell for Date.
OR Change Formula as

Please Login or Register  to view this content.

Hi,
It is already formatted as Date. Change it to General -> clicked OK -> change it back to Date -> clicked OK. Same error.
I changed the formula and changed it back -> pressed Enter. Same error.
I saved and re-opened the file, same error.

This is a really annoying error, i can't seem to get rid of it. Any more ideas would be appreciated.

Saw your new formula suggestion. Tried it but still get the same error. Any other ideas?

Month End Date lookup example ans_2.xlsx
prøv at se på denne:

Tack Bjarne! Sadly that didn't help, i still can't use the formula or even just press F2 on B2 and then ENTER without any changes. It directly results in #N/A.

it works here are you using english or swedis ?

in sheet2 you most chenge it to english month name i use danish my be it is ther

Pl upload your file.

you can download it it in #11

here it is :

Originally Posted by BjarneHansen

it works here are you using english or swedis ?

I realized i forgot to mention something. I use my computer with an English OS and English Regional settings. However, my Office is Swedish so the formulas use Swedish as Excel formulas use the software language. So i also realized i forgot to add the English code "[$-409]" for all the TEXT function references to Month. I have changed them and also changed the month names on Sheet2 to English in your file Bjarne, but still i can't get rid of the #N/A error that is indicating it's the $B$5 references that are incorrect. Suggestions guys?

I found it! I changed all the "mmmm" references to upper-case ("MMMM") and presto, it worked

Thank you for all your help guys!