Date formula question - Pull last day of year (December 31)

[adriennelenker]

Hi all,

I have a date formula issue, it's kind of specific and I couldn't find a solution anywhere.

My current date is 2023-01-31. I need to find the late date of the year of two calendar years before, so 2021-12-31. I used one formula 

DateTimeFormat(DateTimeAdd([date],-2,'Year'),'%Y')

to generate the year 2021.

Now I need to create a formula to generate the last day of this year, 2021-12-31.

What is the best way to do this? Thanks

[ChrisTX]

Which types of dates will you always be starting with?  Only Current Date, where the current month is always January?

For input and output date: which data type do you have/want: Date or String?

Try   ToDate(DateTimeTrim(DateTimeAdd([my date], -1, "month"), "lastofmonth"))

You may need to use DateTimeFormat if the output should be a String.

Chris

[RobertOdera]

Hi, @adriennelenker 

-The last date of any year will always end on 12-31 i.e., Dec always has 31 days

- generate current year: Left([Date],4)

-generate the year in which we will find the current date two calendar years ago: DateTimeFormat(DateTimeAdd([Date],-2,'years'),'%Y')

-generate the last date of the year in which the two years ago calendar date exists: [Year_2CalYrsAgo]+'-12-31'

-convert to Date

Please mark the below as an acceptable solution if it works for you - cheers!

[binuacs]

@adriennelenker One way of doing this

[summit_view]

A few different versions already, but how about something like this, subtract a year, trim to start of year, and then subtract a day.

datetimeadd(
DateTimeTrim(datetimeadd([date],-1,"years"), "year")
,-1,"days")