DateTime Functions - Identifying the Last Day of The Month

[CPelkofer]

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

[patrick_digan · Accepted solution]

@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 solution]

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.

[Luke_C]

Hi @CPelkofer 

It looks like your values are already in date format, so no need to parse them. You might just need to update the data type to be Date if it isn't already.

[patrick_digan · Accepted solution]

@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.

[CPelkofer]

Thanks for the Help!

[CPelkofer]

Thanks for the help!