Formula for populating a Date Cell Based on reference column
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?
Best Answer
-
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).
Answers
-
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).
-
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
-
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
-
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!
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!