I need a cell A to display a date based on the entry in cell B. The entry in cell B is one of 12 different options contained in a drop-down menu. They’re just text entries, but I’ll call them Entry 1, Entry 2, … Entry 12, etc.
For Entries 1 through 10, I need Cell A to display the last day of the month. March 31 for Entry 1, April 30 for Entry 2…October 31 for Entry 10, etc.
For Entry 11, I need it to show January 31st for the following year, and Entry 12 to show February 28 for the following year.
The year will come from a date entry in Cell C.
So, if Cell C shows March 5th, 2019, and Cell B shows Entry 2, then Cell A needs to automatically populate the date April 30, 2019.
If Cell C shows November 4th, 2019 and Cell B shows Entry 11, then Cell A needs to automatically populate the date January 31st, 2020.
I’m sure this is possible. If it’s not too difficult writing such a script, I’d appreciate some help on this.
The first thing to do is make another table that aligns your Entry text with the date is needs to be. Something like this:
Entry 1 March 31 2019
Entry 2 April 30 2019
Entry 3 May 31 2019
(or whatever the dates are.)
From this you’d either use VLOOKUP or INDEX/MATCH (better) to match the Entry value and pull the appropriate date into column A.
To reference next year, you just wouldn’t hard code the dates on your new table. For April 30th 2019, you’d use =DATE(2019, 4, 30). To get next year, you can do something like:
=DATE(YEAR(TODAY())+1, 1, 31)
so it’ll pull this year’s date and then add 1 to it for 2020.
I like the idea of just making another 12 cells that align the text entries into dates. Then, I should be able to create simple IF->THEN entries in Cell C. So if my drop down options are AA1 through AA12, and my aligned dates are placed in AB1 through AB12, then I can just say IF (“Entry 1”, AB1) or something like that.
But, I can’t use =DATE(YEAR(TODAY())+1, 1, 31) however, because the year is not necessarily today’s year. They relevant year comes from the date entry in Cell C. So, if the date in Cell C was November 5th, 2018, and the pull-down entry from Cell B is “Entry 11”, then I need the date in Cell A to populate as January 31, 2019. How do I make it reference just the year from Cell C rather than today’s Year?
Crap. Okay, so aligning the date into another set of rows won’t work, because I can only have it reference one cell in column C. I think I will need a long, drawn out IF->THEN, right?
Because the year in C2 will be different than C1, etc. Entry 1 cannot be aligned with just one particular date.
If someone enters a date into C2, and then selects “Entry 11” (which is just an option from a drop down menu that references hidden cells AA1 through AA12. “Entry 11” is just Cell AA11), then I need Cell A2 to populate January 31st of the following year.
So, Entry 11 will cause a different year to populate based on the year in the C cells, which will all be different.
I think I have it. I at least have some of it. It’s going to take a bit to type out the entire statement. But based on your help, I think I’ll be able to have:
=IF(B4=“Entry 1”,DATE(YEAR(C4),3,31)), IF… and keep going for all 12 possible text entries from the drop down options.
That’s a problem, though–Excel will only let you nest IF loops 7 times. If you have one of the newest versions, you might have a SWITCH function that should work.
For the love of god don’t use IF statements for this. Use VLOOKUP against another table that links the Entry and Date logic. The difference being if you need to change it, it is easier to change it in one spot on the table than in every IF statement.
Here’s a formula I use to get a zodiac sign from a birthday in Cell A1. Maybe it’ll help.
=VLOOKUP(–TEXT(A1,“m.dd”),{1.01,“Capricorn (Ambition and Caution)”;1.2,“Aquarius (Freedom and Excitement)”;2.18,“Pisces (Spirituality and Intuition)”;3.2,“Aries (Adventure and Enthusiasm)”;4.2,“Taurus (Perseverance and Security)”;5.2,“Gemini (Energy and Curiosity)”;6.21,“Cancer (Nurturing and Complexity)”;7.22,“Leo (Confidence and Magnetism)”;8.23,“Virgo (Analysis and Observation)”;9.22,“Libra (Diplomacy and Harmony)”;10.23,“Scorpio (Passion and Extremes)”;11.22,“Sagittarius (Independence and Adventure)”;12.22,“Capricorn (Ambition and Caution)”},2,1)
The numbers are month.day, and the text in quotes is the returned value. Notice that Capricorn is both first and last because it falls across the new year line.
Just one thing to remember. End of month will work better than populating it with the date assuming you want the Feb date to be 29 on leap years like next year.
Okay, I’m hitting the same roadblock with VLOOKUP. If I make a table, I have to either use TODAY as the reference date, or use one particular cell in the C column as the reference. But I want the A cell to use its corresponding C cell to calculate the date. A5 should be looking at C5, etc. What can I put in the Table instead of either =DATE(YEAR(TODAY())+1, 1, 31) or =DATE(YEAR(C4),3,31))? I can’t use “Today” because it may have been a date from last year, and I can’t use only C4 because C5 might have a different year.
Is there a way, instead of referencing a specific C cell, that I can have it reference whichever C Column Cell is in the same row as the target cell?
Value is what you want looked up. It will be the month portion of the string in the cell you want the information from, table is the array you look up in and col_index is which column you want to look at. able will be an array something like
Jan Feb 0
Feb Mar 0
…
Nov Jan 1
Dec Feb 1
It just occurred to me that you can then use a second look up into the next column and then add that number (0 or 1) to the year you started with.
I’m not following at all. I’ve read these posts several times, so it’s not for a lack of trying.
I think you’re saying to create a table that has my 12 Text Entry Options, one in each row. So AA1 through AA12. The next column over, AB, will have the month that corresponds to the Text Entry. So AB 1 will have March, AB2 will have April, etc. And then a third column, AC1 through AC12 will have 0s for AC1 through AC10, and a 1 for AC11 and AC12. With that table set up, you’re saying there is a VLOOKUP formula that I can add to Column A that will look at the text entry in Column B, cross reference that with the Month listed in the AB column, then look at the year from the date entered into Column C and add either a 0 or a 1 to the year based on 0 or 1 in the appropriate AC column, and use all that to populate a new end of the month date in Column A?
If that’s the case, then I’m just not seeing that from “Jan Feb 0”. I think you’ve overestimated my Excel abilities. Please feel free to explain it like I’m 9. I can’t seem to get VLOOKUP to compile different data from several different sources based on other sources. I can get it to find an entry in one column based on the entries in another column, but I can’t get it to create a whole new date using information in multiple sources. I can get it to say “March” if “Entry 1” is entered. That’s simply seeing the text string for Entry 1 in AA1 and looking up the data in AB1 to report “March”. But from there I need it to look at the year in the C column, and then add the appropriate 0 or 1, and finally report a complete End of the Month date with the correct year.
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.
What’s the exact relationship between the Column C date, the Entry Value, and the End Date?
For example, you said March 5th, 2019 + Entry 2 = April 30, 2019. Is that because Entry 2 is always April 30 2019 or because Entry 2 is always the month end from the original date?