Date formatting for last day of month

[tiverson]

We use a fiscal calendar that begins in October as Period 1

I have data (column B) that only lists the year and period and I want to create a new column (column D) that will show the ending day of the period listed in the Date column.

[chukleswk · Accepted solution]

You can use the following formula to find the last day of the month: 

datetimeadd(datetimeadd([Date] + '-01',10,'months'),-1,'days')

 

If you want it in the format that you have shown above then you can change the data type to a string and use the following formula:

datetimeformat(datetimeadd(datetimeadd([Date] + '-01',10,'months'),-1,'days'),'%m/%d/%Y')

 

 

[drew9 · Accepted solution]

Hi @tiverson , 

I was able to accomplish this with a pair of lookup tables 🙂

Hope this is helpful!

[Qiu · Accepted solution]

@tiverson 
A few simple tools should be able to do the work.
DateTimeTrim is a good fit here.