Date Calculation Questions

Bethany Garcia

This is a two-parter.

Part 1: I have a dropdown field where users select a month and year in the format of Jan-2021, Apr-2022, 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 drop-down. 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.

Paul Newcome

@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.

Bethany Garcia

@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!

L_123

@Bethany Garcia @Paul Newcome

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)

Bethany Garcia

@L_123 thank you for that formula, but what I'm in need of is a formula that can take a value of a drop-down field where users select a month and year in the format of Jan-2021, Apr-2022 (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?

L_123

@Bethany Garcia @Paul Newcome

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

1-2021

instead of

Jan-2021