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.

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.

Lori Flanigan

Thank you, Paul! I appreciate your expertise and spot-on formula. :)

Lori

Paul Newcome

Happy to help. 👍️