DateTime Functions - Identifying the Last Day of The Month

Question

Hi There,

I am creating a new column in the dataset for Termination dates to be the end of that month in time "Termination Month", but if the termination date was already the last day of that month I want it to show up as the last day of the following month. I know I need an IF function in lieu of the DateTime, but I am having trouble just purely identifying the termination date being the last day of that month. I have attached a sample output of what I am trying to accomplish, thanks in advance!

IF (date equals last day of that month)

THEN (its reporting 2 months ahead minus one day, to get the termination month reported as the following month)

ELSE (reporting the last day of the month for the Termination Date)

ENDIF

Question Example.xlsx

patrick_digan · Accepted Answer

@CPelkofer I would first convert the term date to an alteryx date, and then do a formula like:

If Left(DateTimeTrim([Term Date (Date)],"lastofmonth"),10) = [Term Date (Date)] 
then 
DateTimeTrim(DateTimeAdd([Term Date (Date)],1,"days"),"lastofmonth")
else
DateTimeTrim([Term Date (Date)],"lastofmonth")
endif

The left 10 is necessary as DateTimeTrim lastofmonth adds the time component

.

CPelkofer · Accepted Answer

Hi Patrick,

I am getting a parse error for this due to me not giving you how the actual figures were formatted in my data compared to the example I attached to my question. I apologize. The actual format they are currently are in are shown below.

patrick_digan · Accepted Answer

@CPelkofer No worries! If they're already in that date format, you can just change all the references of [Term Date (Date)] to [Termination Date]. You could then remove that first formula that was creating the Term Date (Date) field.