Help with Excel formulas.

[Bear_Nenno]

Entry 2 is always the end of April of the year entered in the Column C date. Entry 11 is always January of the following year. Entry 12 is always February of the following year. It would probably be easier if I could just enter the month instead of the entry text but that won’t work for my purpose. If there’s a way to tell excel to display one thing but calculate another, then that might help. So that the cell displays Entry 2, but internally sees that cell as (End of April, Year+0), etc.
I’ve made a screen shot that helps explain the problem. Keep in mind there will be an unlimited number of rows.

[GreysonCarlisle]

@Bear_Nenno

To be clear, Column B will have over a hundred rows. Each cell in that column has a drop down menu with 12 options. I’ve used Column AA to set up the options for the drop menu. Column C will also have hundreds of rows. Each cell in that column will have a unique date entered with Month, Day, Year.
I need the hundreds of cells in column A to report a date that takes the text entry in Column B, converts that to a particular month, and then looks at the year in column C, and then (based on the month) adds 1 to the year or not.

Ah, sorry. I thought Column B had dates. So ignore the B1 reference in my post. You can get the month and day from Column C, I believe, so use that instead.

[GreysonCarlisle]

@Bear_Nenno

I’ve made a screen shot that helps explain the problem. Keep in mind there will be an unlimited number of rows.

Imgur: The magic of the Internet

Thank you!

[Zakalwe]

Try this in Cell A2 and copy down…

=DATEVALUE(CONCATENATE(VLOOKUP(B2,$AA$2:$AC$13,2,FALSE)," “,
DAY(EOMONTH(DATEVALUE(CONCATENATE(VLOOKUP(B2,$AA$2:$AC$13,2,FALSE),” ","1, ",YEAR(C2)+VLOOKUP(B2,$AA$2:$AC$13,3,FALSE))),0)),
", ",YEAR(C2)+VLOOKUP(B2,$AA$2:$AC$13,3,FALSE)))

I will note that based on the data in your sample sheet, A5 yields a weird value of 8/31/19 even thought C5 is in September. But since B5 sets the month and that’s set to return August…well, there it is.

[Palooka]

You’re pretty close. You just need the formula syntax nailed down.

This is how I’d do it without thinking about it too much from an optimization perspective.

=EOMONTH((DATE(YEAR(C2)+INDEX(EntryTypes!C:C, MATCH(Dates!B2, EntryTypes!A:A, 0)), INDEX(EntryTypes!B:B, MATCH(Dates!B2, EntryTypes!A:A, 0)), 1)), 0)

With my data setup as follows: Imgur: The magic of the Internet

[GreysonCarlisle]

If you can change the values in Column AB to numbers (1 = Jan, 2 = Feb, etc.), then this formula will hopefully work for the cells in Column A:

=IF(OR(B2=“Entry 11”,B2=“Entry 12”),EOMONTH(DATE(YEAR(C2)+1,VLOOKUP(B2,$AA$2:$AC$65535,2),1),0),EOMONTH(DATE(YEAR(C2),VLOOKUP(B2,$AA$2:$AC$65535,2),1),0))

Copy/paste into Cell A2, then grab the corner and drag it down as far as you need it to go.

Will work for up to 65,535 rows.

But like I said, I can’t test it on this machine, so best of luck!

[Bear_Nenno]

@Zakalwe

Try this in Cell A2 and copy down…

=DATEVALUE(CONCATENATE(VLOOKUP(B2,$AA$2:$AC$13,2,FALSE)," “,
DAY(EOMONTH(DATEVALUE(CONCATENATE(VLOOKUP(B2,$AA$2:$AC$13,2,FALSE),” ","1, ",YEAR(C2)+VLOOKUP(B2,$AA$2:$AC$13,3,FALSE))),0)),
", ",YEAR(C2)+VLOOKUP(B2,$AA$2:$AC$13,3,FALSE)))

I will note that based on the data in your sample sheet, A5 yields a weird value of 8/31/19 even thought C5 is in September. But since B5 sets the month and that’s set to return August…well, there it is.

Yes, the dates are not always going to be ahead of the manually entered date.

Oh crap, but that does bring up another issue I might run into. I won’t worry about that just now. I might have to bring it up later. I’m going to enter these formulas and see how it plays out. I’ll keep you all posted. Thanks!!

[GreysonCarlisle]

@GreysonCarlisle

$AC$65535

Will work for up to 65,535 rows.

Ugh. You can change the instances of $AC$65535 to $AC$13. It’ll work for as many rows as you have. Brain fart.

[Zakalwe]

@Palooka

You’re pretty close. You just need the formula syntax nailed down.

This is how I’d do it without thinking about it too much from an optimization perspective.

With my data setup as follows: Imgur: The magic of the Internet

Yeah, we got all the same answers, you just optimized better than I did. I brute forced it in and took the first answer I got. I was planning to look at it again tomorrow, but I think yours works fine.

[Bear_Nenno]

Okay, MONEY! There were a few exceptions when a January or February month actually pertained to a previous year. This was actually a simple fix. I just added two more entries to the drop down menu which differentiated the scenarios and made the minor change to your formula. Thanks, everyone. You guys are the best!