Help with formula

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")

Capture.JPG

sample_workflow.yxmd

Help

Accepted answers

Qiu

@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.

sample_workflow-r1.yxmd

All comments

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.

sample_workflow-r1.yxmd

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

Book1.xlsx

sample_workflow.yxmd

tried.JPG

Qiu

@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.

sample_workflow-r1.yxmd

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?

sample1.JPG

sample2.JPG

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335