# Help with Excel formulas. **URL:** \ **Category:** Factual Questions\ **Created:** [July 28, 2019, 9:49pm UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771 "2019-07-28T21:49:35Z")\ **Posts on this page:** 10\ **Page:** 2
**Author:** ![Bear\_Nenno](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bear_nenno/32/3358_2.png) [@Bear\_Nenno](https://boards.straightdope.com/u/Bear_Nenno)\ **Post date:** [July 29, 2019, 2:48am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/21 "2019-07-29T02:48:45Z")
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. [![](https://i.imgur.com/2ufalkC.jpg) ](https://i.imgur.com/2ufalkC.jpg) ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [July 29, 2019, 2:51am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/22 "2019-07-29T02:51:14Z")
> [@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. ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [July 29, 2019, 2:52am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/23 "2019-07-29T02:52:34Z")
> [@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](https://i.imgur.com/2ufalkC.jpg) Thank you! ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [July 29, 2019, 3:20am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/24 "2019-07-29T03:20:32Z")
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. ---
**Author:** ![Palooka](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/palooka/32/2902_2.png) [@Palooka](https://boards.straightdope.com/u/Palooka)\ **Post date:** [July 29, 2019, 3:24am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/25 "2019-07-29T03:24:20Z")
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](https://imgur.com/a/LKTc4hI) ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [July 29, 2019, 3:25am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/26 "2019-07-29T03:25:12Z")
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! ---
**Author:** ![Bear\_Nenno](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bear_nenno/32/3358_2.png) [@Bear\_Nenno](https://boards.straightdope.com/u/Bear_Nenno)\ **Post date:** [July 29, 2019, 3:32am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/27 "2019-07-29T03:32:22Z")
> [@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!! ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [July 29, 2019, 3:36am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/28 "2019-07-29T03:36:16Z")
> [@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. ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [July 29, 2019, 3:39am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/29 "2019-07-29T03:39:30Z")
> [@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](https://imgur.com/a/LKTc4hI) 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. ---
**Author:** ![Bear\_Nenno](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bear_nenno/32/3358_2.png) [@Bear\_Nenno](https://boards.straightdope.com/u/Bear_Nenno)\ **Post date:** [July 29, 2019, 3:45am UTC](https://boards.straightdope.com/t/help-with-excel-formulas/837771/30 "2019-07-29T03:45:51Z")
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! [Previous page](https://boards.straightdope.com/t/help-with-excel-formulas/837771.md?page=1)