Date Calculation Questions
This is a twoparter.
Part 1: I have a dropdown field where users select a month and year in the format of Jan2021, Apr2022, where it is always a 3 character month followed by 4 character year. I need to return the date that is the last Sunday of the month that is selected in that dropdown. Any ideas on how to do that? I can use helper columns but just can't figure out how to get there.
Part 2: Once I get that date that is the last Sunday of the month, I then need to calculate another date that is either 3 months or 6 months from that date. I need to do it by increasing the month of the date by 3 or 6. I've figured out that I can use the formula =DATE(YEAR([Test Date]@row) + 1, MONTH([Test Date]@row), DAY([Test Date]@row)) to get the date that is 12 months away, but when I try to do the same for the month in this formula I get an error if the resulting date goes into the next year.
Answers

@L_123 I believe I remember you coming up with a rather brilliant solution for finding the first Monday (I think) of the month using your genius with numbers. Any thoughts on finding the last Sunday??
@Bethany Garcia The second part I can help with...
=IFERROR(DATE(YEAR([Test Date]@row), MONTH([Test Date]@row) + 6, DAY([Test Date]@row)), DATE(YEAR([Test Date]@row) + 1, MONTH([Test Date]@row)  6, DAY([Test Date]@row)))
For the 3 months bit, you would change the first 6 to 3 ( + 3 ) and the second 6 to 9 (  9 ). Basically the two numbers should always add up to 12.

@Paul Newcome that worked perfectly, thank you!
@L_123 please let me know if you have any thoughts on how to get the last Sunday of the month!

Just saw this and typed something up quick. It could be optimized for sure, but it works.
=DATE(YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 7), MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 7), 1)  WEEKDAY(DATE(YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 7), MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 7), 1)) + 1
(find the first day of the next month, subtract the weekday of said first day, and add one to the result)

@L_123 thank you for that formula, but what I'm in need of is a formula that can take a value of a dropdown field where users select a month and year in the format of Jan2021, Apr2022 (where it is always a 3 character month followed by 4 character year) and return the date that is the last Sunday of that month. Any ideas on how to do that?

Only read the comments, not the main question. Still a similar concept though, just with a couple really long conditional statements. (dropdown column name is A. Change your column name to "A", post this formula, then change it back to what you want)
=DATE(IF(LEFT(A@row, 3) = "Dec", 1, 0) + VALUE(RIGHT(A@row, 4)), IF(LEFT(A@row, 3) = "Jan", 2, IF(LEFT(A@row, 3) = "Feb", 3, IF(LEFT(A@row, 3) = "Mar", 4, IF(LEFT(A@row, 3) = "Apr", 5, IF(LEFT(A@row, 3) = "May", 6, IF(LEFT(A@row, 3) = "Jun", 7, IF(LEFT(A@row, 3) = "Jul", 8, IF(LEFT(A@row, 3) = "Aug", 9, IF(LEFT(A@row, 3) = "Sep", 10, IF(LEFT(A@row, 3) = "Oct", 11, IF(LEFT(A@row, 3) = "Nov", 12, IF(LEFT(A@row, 3) = "Dec", 1, 0)))))))))))), 1)  WEEKDAY(DATE(IF(LEFT(A@row, 3) = "Dec", 1, 0) + VALUE(RIGHT(A@row, 4)), IF(LEFT(A@row, 3) = "Jan", 2, IF(LEFT(A@row, 3) = "Feb", 3, IF(LEFT(A@row, 3) = "Mar", 4, IF(LEFT(A@row, 3) = "Apr", 5, IF(LEFT(A@row, 3) = "May", 6, IF(LEFT(A@row, 3) = "Jun", 7, IF(LEFT(A@row, 3) = "Jul", 8, IF(LEFT(A@row, 3) = "Aug", 9, IF(LEFT(A@row, 3) = "Sep", 10, IF(LEFT(A@row, 3) = "Oct", 11, IF(LEFT(A@row, 3) = "Nov", 12, IF(LEFT(A@row, 3) = "Dec", 1, 0)))))))))))), 1)) + 1
*btw you can shorten this quite a lot if you change your dropdown to
12021
instead of
Jan2021
Help Article Resources
Categories
Check out the Formula Handbook template!