Add Last Day Of Previous Month to .xlsb export

I am trying to add a date to the end of the file name when I export data out of Alteryx as an Excel Binary .XLSB file. When I run the workflow, I always want the date to be the last calendar day of the previous month in the file name. (I.e. Myfilename 2023 02 28.xlsb) What formula should I use? I can't seem to get the right output as it is always adding time to the end like Myfilename 2023 02 28 00:00:00.xlsb). Any help would be greatly appreciated!

Datasets

Accepted answers

apathetichell

formula tool - create newfieldfilename

"Myfilename "+datetimeformat(datetimetrim(datetimeadd(datetimenow(),-1,"months"),"lastofmonth"),"%Y %m %d")+".xlsb" - then replace your entire path with that field.

note - kind of winging the formatting but basically take today - subtract a month, use datetimetrim with lastofmonth and then use the formatting tool as needed.

All comments

apathetichell

formula tool - create newfieldfilename

"Myfilename "+datetimeformat(datetimetrim(datetimeadd(datetimenow(),-1,"months"),"lastofmonth"),"%Y %m %d")+".xlsb" - then replace your entire path with that field.

note - kind of winging the formatting but basically take today - subtract a month, use datetimetrim with lastofmonth and then use the formatting tool as needed.

RCern

Do I add just the name I want or the entire file path like C:\Joe\Documents\myfilename.xlsb? Since it's an .xlsb file, it puts a Sheet name at the end of the file name when I use the output tool. How do I compensate for that in the formula?

apathetichell

"C:\Joe\Documents\Myfilename"+....+".xlsb|Sheet1"

Full path is needed. if your directory and sheets are variables it would be [directory]+...+"|`"+[sheet] +"`"

RCern

Hmmm... Here is what my formula reads, but it is still outputting the file name wrong:

"A:\Report\Combined Report\Combined + address state "+ datetimeformat(datetimetrim(datetimeadd(datetimenow(),-1,"months"),"lastofmonth"),"%Y %m %d") + ".xlsb|||Sheet1"

The result of this formula is:

Sheet1A__Report_Combined Report_Combined + address state 2023 02 28

I am expecting

Combined + address state 2023 02 28.xlsb

apathetichell

double check bottom corner of your output data - you want this on "replace entire path" my hunch is this on append to sheet name.

RCern

I made the change as you requested (see attached) and it still produced the same file output 😪

2023-03-09_10-35-32.png

apathetichell

my hunch is you are modifying this field somewhere else in your workflow - ie you have a second formula somewhere adding your filename to your sheet name.

can you add a browse tool before your output data tool and see what you are feeding in?

RCern

The formula tool containing this formula is placed right before the output tool. This formula doesn't exist anywhere else in the workflow. see attached

2023-03-09_10-35-32.png

apathetichell

This could be a super weird .xlsb glitch. Would you mind trying to run as a .xlsx and seeing if this would work as set up? Sorry I'm not 100% sure if there is a glitch issue with .xlsb files - and if so you might need a batch macro work around.

apathetichell

oh no problem- tbh this is fascinating - you would have had different results for a .xlsx - so this is good to read. (.xlsx would have said sheet already exists or something)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703