Convert Time/Date to Date Then Convert to Future Date
Data that I'm importing via Data Shuttle includes a date field (named Date Issued) with this format:
2022-01-22T10:30:55.999999889172613350
I added another date field (named Issued Date) where I added this formula to pull out the date only:
=IF([Date Issued]@row <> "", MID([Date Issued]@row, 6, 2) + "/" + MID([Date Issued]@row, 9, 2) + "/" + LEFT([Date Issued]@row, 4), "")
That works great; however, I need another date column (named Reissue Date) in which the date is 3 years in the future of the Issued Date. I have a Renewal Years helper column that has 3 in it. I've tried this formula, but it is not working:
=IFERROR(DATE(YEAR([Issued Date]@row) + [Renewal Years]@row, MONTH([Issued Date]@row), DAY([Issued Date]@row)), "")
I'd appreciate any help about correcting this.
Thank you!
Lori
Best Answer
-
The formula stripping out the date is outputting a text string that just looks like a date. it is not outputting data that is stored as an actual date. To do this, you would need to incorporate the DATE function.
DATE(yyyy, mm, dd)
=IF([Date Issued]@row <> "", DATE(VALUE(LEFT([Date Issued]@row, 4)), VALUE(MID([Date Issued]@row, 6, 2)), VALUE(MID([Date Issued]@row, 9, 2))))
Now that you have it stored as a date value on the back end, you can reference this column in the formula that you want to add time to.
Answers
-
The formula stripping out the date is outputting a text string that just looks like a date. it is not outputting data that is stored as an actual date. To do this, you would need to incorporate the DATE function.
DATE(yyyy, mm, dd)
=IF([Date Issued]@row <> "", DATE(VALUE(LEFT([Date Issued]@row, 4)), VALUE(MID([Date Issued]@row, 6, 2)), VALUE(MID([Date Issued]@row, 9, 2))))
Now that you have it stored as a date value on the back end, you can reference this column in the formula that you want to add time to.
-
Thank you, Paul! I appreciate your expertise and spot-on formula. :)
Lori
