Create/name new excel tab for new month

[StephenT]

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)

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

[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 · Accepted solution]

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.

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