Add Last Day Of Previous Month to .xlsb export

[RCern]

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!

[apathetichell · Accepted solution]

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 😪

[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

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