on 11-03-2016 03:03 PM - edited on 07-27-2021 11:35 PM by APIUserOpsDM
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?
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:
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.
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!
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")
Sure thing, @krrichardson94. Nice work!