Create/name new excel tab for new month

greetings all

I have workflow tht;s run twice a week, an am wondering is there if a way i have have the workflow create a new tab in the Excel sheet when evever a new month tartes then continue to add date to the tab until the new months starts.

The field I am using is called "Last day of work" and is in the format on yyyy/mm/dd (but this format can change if needed)

Workflow

Accepted answers

philipmannering

Hi @StephenT

You can use this formula to get your sheet name in the specified format,

DateTimeFormat(DateTimeParse([Last day of work], '%Y/%m/%d'), 'Departers - %b %Y')

See example attached.

create new sheet if not exists.yxmd

All comments

philipmannering

Hi @StephenT

Try something like this...

You may not need the Count Records and Append Fields Tools - these are to prevent writing to the same excel at the same time... which would only occur if you had multiple months in your data set at any one time. Solution attached.

create new sheet if not exists.yxmd

StephenT

Thanks @PhilipMannering

I have ran your workflow but I get an error "Error: Join (9): The field "Sheet Names" is not contained in the record." Do i need to create a new tab in the XLSX file with the list of tab names ?

Also I am now lookin for the tab for each month to be shown as 04/2023 (i.e april 2023)

Thr file (when set up) will contain 12 months (financial year) of data, so the step of appending dat will be needed.

StephenT

Sorry @PhilipMannering
One more question. Do I need to create an extra field for record number ?

philipmannering

You need to create a field for Filepath and a field for Sheet Name (this would be the month in this case). See my attached example. I wouldn't have a forward slash in your sheet names though, this might error.

No field for record number is required.

StephenT

PhilipMannering

I finally got it worked out - your solution is highly appreciated but have 1 more question, then I will accept it as the solution :
If it possible to have the new tab names s created in a specific format (e.g. Departers - Mar 23 or Departers - Mar 2023) as I will need to implement your solution into several workflows as the tabs in my existing sheets are based on the month the 'last day of work' not based on the date itself.
Should I create an extra field using the date time tool ?

philipmannering

Hi @StephenT

You can use this formula to get your sheet name in the specified format,

DateTimeFormat(DateTimeParse([Last day of work], '%Y/%m/%d'), 'Departers - %b %Y')

See example attached.

create new sheet if not exists.yxmd

StephenT

Thanks again @PhilipMannering

Your solution wouks perfectly (after mergin tinto my workflows 👍 ).I'll now flag it as "accept as solution"

StephenT

Hi @PhilipMannering
your solution has worked seamlessly for the past month however I just noticed the workflow keeps trying to append date to the Excel sheet instead of creating a new sheet if one does not exist, and does not append to the existing sheet. I have made one change to your solution by adding a text input tool containing the month names. Any ideas ?

StephenT

Hi @PhilipMannering
I am still having an issue with this solution where the data is not being appended to the existing sheet but is trying to create a new tab. The only change I made is changed the tool containing the sheet names to a "text Input" tool. do you have any idea as to what's going on ?

alteryx 2023-08-07 141254.gif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703