Date Calculation - Need to calculate 1 day less

[Rich5678]

Hi,

I have a date field called AS_OF_DATE where data is stored in M/D/YYYY format.

Currently, this field has dates such as 

2/1/2022

3/1/2022

4/1/2022

However, I require Month-end dates such as

1/31/2022

2/28/2022

3/31/2022

I tried the conversion, using following formula

Date(Date#([AS_OF_DATE],'M/D/YYYY')-1,'M/D/YYYY')

However, even when I do this, I still get the same dates as original records.

Can someone help

[Digvijay_Singh]

Monthend([AS_OF_DATE],-1) should do the needful. You may use Date() around that in case you need formatting

[MayilVahanan]

Hi

Try like

Date(Floor(MonthEnd(AS_OF_DATE)), 'M/DD/YYYY')

[vinieme12]

As below

MonthEnd(Date#(FieldName,'MM/DD/YYYY'),-1) as MonthEndDate