Formula for populating a Date Cell Based on reference column

EmilyD

Hi,

Hoping someone can help me write a formula for a date column. I am very new to writing formulas and could use some guidance. I would like to have a date populated based on previous data. Expires column is where I am looking to insert the formula. If the date is 3/21/22 and length of access is 6 months, I would like the Expires cell to populate with the appropriate date. Can anyone help me?

Paul Newcome

Give this a try:

=IFERROR(DATE(YEAR([Request Date]@row) + ROUNDDOWN((MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) / 12, 0) + IF(IF(MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12) = 0, 12, MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12)) = 12, -1) - IF(AND(ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) - MONTH([Request Date]@row) <> 12, VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)) < 0, ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) > MONTH([Request Date]@row)), 1, 0), IF(MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12) = 0, 12, MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12)), 1), DATE(IF(MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) < 1, YEAR([Request Date]@row) - 1, YEAR([Request Date]@row)), IF(MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) < 1, MONTH([Request Date]@row) + (12 - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)))), MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)))), 1))

The above will accommodate any number of months (positive or negative) assuming all numbers entered are in months (24 Mo. instead of 2 Y).

Paul Newcome

Give this a try:

=IFERROR(DATE(YEAR([Request Date]@row) + ROUNDDOWN((MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) / 12, 0) + IF(IF(MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12) = 0, 12, MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12)) = 12, -1) - IF(AND(ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) - MONTH([Request Date]@row) <> 12, VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)) < 0, ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) > MONTH([Request Date]@row)), 1, 0), IF(MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12) = 0, 12, MOD(MONTH([Request Date]@row) + VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)), 12)), 1), DATE(IF(MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) < 1, YEAR([Request Date]@row) - 1, YEAR([Request Date]@row)), IF(MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))) < 1, MONTH([Request Date]@row) + (12 - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)))), MONTH([Request Date]@row) - ABS(VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1)))), 1))

The above will accommodate any number of months (positive or negative) assuming all numbers entered are in months (24 Mo. instead of 2 Y).

EmilyD

Thank you, Paul! This is amazing and saves me so much time and effort on the end user. My ability is limited to basic countif formulas. To do this would have taken me months if not years to figure out 😉

Much appreciated!!

-Emily

Paul Newcome

Happy to help. 👍️

It honestly took me a while to figure that one out. When I did, I saved a copy of it in a dedicated sheet so I could just tweak it then grab it much more quickly. If I didn't have the copy, it would probably take me quite some time to figure it all out again. Hahaha

EmilyD

You are so kind to take the time! I will take your advice here and save the formula to reference, especially if this sheet goes away for any reason!

Paul Newcome

If you do a search in the Community for EDATE, you should be able to find something that will at least take care of a positive number of months. I do remember helping someone (quite) some time ago on negative months which then got combined with the positive months into the formula you see here.

The one thing to keep in mind though... In all of the other solutions as well as my original formula, the number of months is stored in the sheet as an actual number. In your screenshot you have them as text strings, so I had to adjust each spot that references the number of months with

VALUE(LEFT([Length Of Access]@row, FIND(" ", [Length Of Access]@row) - 1))

You may not see that in other solutions and they may not work for you directly "out of the box" because of the text vs numbers bit.