Convert Time/Date to Date Then Convert to Future Date

Lori Flanigan

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

Paul Newcome

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.