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")
Solved! Go to Solution.
Hi @coryanthony
Try this formula instead
[Account] + "_" + DateTimeFormat(DateTimeTrim(DateTimeParse([Entry Date], "%m/%/%Y"),"lastofmonth"),"%Y-%m-%d")
@coryanthony
We can use the Summarize tool to get the most recent date after converting the [Entry Date] to a Date data type field.
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 |
@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.
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.
@coryanthony
Yes, you can get the format for date as you like 😁
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?
@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.