First of Year and Last of Year

[DanC]

Question

The Formula Tool has a built in function to calculate the first day of the month (DateTimeTrim([UserDate],"firstofmonth")) and last day of the month(DateTimeTrim([UserDate],"lastofmonth"))of a date. How can I calculate the first and last day of the year of a date?

Answer

Alteryx outputs the first day of the month as YYYY-MM-01 00:00:00 and the last day of the month as YYYY-MM-31 23:59:59 (substitute 31 for the appropriate last day for given month). In order to replicate that at the year level, we can use the following formulas:

• First of Year:DateTimeFormat([TestDate],"%Y-01-01 00:00:00")
• Last of Year:DateTimeFormat([TestDate],"%Y-12-31 23:59:59")

Keep in mind that this formula expects your dates to be in the Alteryx native date format of YYYY-MM-DD HH:MM:SS and have the type of DateTime.

[krrichardson94]

Thanks for this. I do have an additional question. Is there a expression/formula that would calculate the last day of the year where there is no date field within the Input Data file? Based on your example, it looks like you have a field (TestDate) that contains a date within your Input Data file that allows you to calculate the last of the year.

[DanC]

Hi @krrichardson94!

Thanks for your question. You can simply substitute DateTimeToday() for the test date. That will calculate the end of the year for the year of today's date. Is that what you had in mind?

Thank you!

[krrichardson94]

PERFECT!!!! I actually have to calculate the previous year end date so I actually updated the formula/expression to the following below. This gives me 2017-12-31 23:59:59 which I can convert to a MM/DD/YYYY format using the DateTime tool. Thanks again for your help!!!!

DateTimeAdd(DateTimeFormat(DateTimeToday(),"%Y-12-31 23:59:59"),-12, "MONTHS")

[DanC]

Sure thing, @krrichardson94. Nice work!