Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

returning the 1st day of the month based on a date field

10005970
6 - Meteoroid

Hi Alteryx Community.

 

I have a Transaction date field in a workbook (the format is standard date Time YYYY-MM-DD) I want to add a column that will display the first day of the month in the same date time format. see example below

 

thanks in advance

 

EG

Transaction Date1st day of Transaction month
2021-12-202021-12-01
2021-12-292021-12-01
2022-01-072022-01-01
2022-01-082022-01-01
2022-02-182021-02-01
5 REPLIES 5
patrick_digan
17 - Castor
17 - Castor

Hi @10005970 How about something like Left([Transaction Date],7)+"-01"

AngelosPachis
16 - Nebula

Hi @10005970 ,

 

In a formula tool, try the following expression

 

DateTimeTrim([Transaction Date],"firstofmonth")

 

That should give you what you're looking for.

 

Cheers,

Angelos

atcodedog05
22 - Nova
22 - Nova

Hi @10005970 

 

2 more ways

 

atcodedog05_1-1645534600196.png

 

Hope this helps : )

 

10005970
6 - Meteoroid

That worked perfect thank you.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@10005970 ,

 

Here are some lasting postings concerning dates:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Marquee-Crew-s-Guide-to-Dates/ta-p/...

 

https://community.alteryx.com/t5/Engine-Works/MarqueeCrew-s-Guide-to-Avoiding-Date-Frustration/ba-p/...

 

They are easier found using google ( @WillM )

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels