Help with formula

[coryanthony]

Please HELP!

I need a formula that creates a column just for file name. The objective is to have the last day of the most recent month in data set. So if the Max date is "2023-10-15" the the column should be 2023-10-31. 

Field [Entry Date] has different dates going back to 2013. But in this field, the most recent date is 2023-10-15. Therefore, i want a column to say 2023-10-31. 

For [Entry Date] 9/30/2013 - i want it to display 2023-10-31 (which is the MAX date it the field (10-15-2023))

Current formula - [Account] + "_" + DateTimeFormat(DateTimeAdd(DateTimeAdd(DateTimeTrim([Entry Date], 'month'), 1, 'month'), -1, 'day'), "%Y-%m-%d")

 

[DavidSkaife]

Hi @coryanthony 

Try this formula instead

[Account] + "_" + DateTimeFormat(DateTimeTrim(DateTimeParse([Entry Date], "%m/%/%Y"),"lastofmonth"),"%Y-%m-%d")

[Qiu]

@coryanthony 
We can use the Summarize tool to get the most recent date after converting the [Entry Date] to a Date data type field.

[coryanthony]

HI @DavidSkaife & @Qiu 

Thank you for responding. I place a new sample file with input for better explanation. 

Based on the [Entry Date], e.g. below. New field should be "32310_10/31/2023.
To clarify. Max[Entry Date] then get the last day of that month. 

Since 10/15/2023 is the last day, Field would then be [Account] + 10/31/2023.

Account	Entry Date
32310	10/15/2023
32310	10/15/2023
32310	9/27/2017
32310	10/31/2017

[Qiu · Accepted solution]

@coryanthony 
We just need to change a bit on the DateTime Format from my last reply.
And honestly, I dont like to use the "/" in the fiename or so, since it will cause issue sometimes in Windows OS.

[coryanthony]

Hi @Qiu 

Got it. It does not have to be that format. But i now understand. I can get desire file name by using the summarize tool then bring it back in. Then apply formula. 

[Qiu]

@coryanthony 
Yes, you can get the format for date as you like 😁

[coryanthony]

@Qiu 

Thank you so much. One last question. I am using the field name [FileName] for saving over excel file in the output folder. 

The option works perfectly for cvs files but not excel. any ideas as to why?

[Qiu]

@coryanthony 
Try to change the Output Options to "Over Sheet or Range".
I will guess you can only get one sheet in the output, right? because everytime new file will be generated for each Sheet when you selected the "Overwrite File" option.