Get Current and Prior Year Dates from Data

I am trying to use the Data that I have currently and transform the Fiscal End Date Column into two additional columns where one has the current Year of 2017 and another column that has the prior year of 2016. For example I need Dates for Row 1 to look like 2017-04-30 and 2016-04-30. Is there a simple way / formula to get these new years added to the dates. The Month and Days of the date are good I just want to change the year. Thanks so much.

Preparation

Accepted answers

Joe_Mako

Would these formula expressions be what you are looking for, they are relative to the current system clock date:

Current:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))
,"year")

Prior:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))

-1
,"year")

All comments

Joe_Mako

Would these formula expressions be what you are looking for, they are relative to the current system clock date:

Current:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))
,"year")

Prior:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))

-1
,"year")

MLRM

Yeah that works perfect. I just had to change the Formulas to DateTimeAdd but other than that it works great. So to double check these formulas are now dynamic in that when it becomes 2018 the current year and Prior Year will also change accordingly then. Thanks so much for the help.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167