Combine week column, FY year and FY month.

Question

Hey!
I have a question regarding combining 3 non date, consisting of Fiscal Year, Fiscal Month and week number into a date column.

I have linked a sample dataset in this, but to elaborate with an example:

As of today the 3 columns are "FY 21/22",  "05 - November" and "Week 49". This I would like to convert into a date column, which start on the first day of week. I.e. date column is "2021-29-11".

The tricky part is when it comes to January 2022, for example with columns "FY 21/22", "07-January" and "Week 4", it should say "2022-01-25".

Can anybody help on how to convert these 3 into a date column

Fiscal year dates.xlsx

T_Willins · Accepted Answer

Hi @sebastian123Han,

Attached is a workflow that meets your requirements.  One note on your data - Week 49 of 2021 is December, not November if one starts counting Week 1 as the first Monday in January.  This workflow accommodates your July start fiscal year.  There are more efficient ways to parse the data and write the formulas, but I left it this way so you could see the steps.  Let me know if you have questions.