End of the month for whole year

[Ecaterina]

Hello,

I want to calculate the end of month for a whole year. How can I do this?

Thanks 

[MarqueeCrew · Accepted solution]

@Ecaterina ,

 datetimetrim(). Is your friend:

DateTimeTrim(dt,t)

Remove unwanted portions of a date/time and return the modified date/time.

Parameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

t: Trim type. Options include:

• firstofmonth:  trim to the beginning of the month (this does the same as month)
• lastofmonth: extend to one second before the end of the last day of the month
• year: trim to midnight on January 1st.
• month: trim to midnight at the first day of the month
• day: trim to the day (i.e., midnight). This will convert a DateTime to a day with a time of zero (not a date).
• hour:  trim to the hour
• minute:  trim to the minute.

cheers,

 mark

[RolandSchubert · Accepted solution]

Hi @Ecaterina ,

you could use DateTime functions (DateTimeParse, DateTimeAdd) combined with Generate Rows tool to create a list of EOM dates. I've attached a sample workflow.

Best,

Roland

[Ecaterina]

@RolandSchubert

 Could you attache a sample? 

Thanks

Ecaterina

[RolandSchubert · Accepted solution]

Seems I missed to attach the workflow ...

[Ecaterina]

Thanks.  it is work well

[ChrisTX]

Look at these functions:
  DateTimeLastOfMonth()
  DateTimeFirstOfMonth() - then subtract one day

Use the Generate Rows tool

In this post:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-drop-down-list-boxs-to-populate-...

there is a macro that calculates many common dates like:
period
year
day
first day of month
last day of month
isWithin5DaysOfClose
isWithin5DaysOfOpen
day of week text
isWeekday
isWeekend
day of week text short
day of year
month text
month text short
qtr_txt
qtr_num
isEndOfMonth
isFirstOfMonth
isClosingPeriod
isOpeningPeriod
weekOfYear