Pivoting/Processing Data in Google Sheets

Question

I am trying to pivot data that has column headers with Dates but have quantitative values in regards to hours worked.

The data currently looks like this:

Name     Task     January Hours Worked February Hours Worked March Hours Worked
John Doe Admin    10                   10                    10

I want the data to look like this:

Name     Task  Date     Hours Worked
John Doe Admin January  10
John Doe Admin February 10
John Doe Admin March    10 

Is there any, more simplistic way of recreating this? I know it's not as easy as just pivoting fields as the 'Date' and 'Hours Worked' would be new fields hypothetically speaking. I'm working in the Google Sheets environment, but also have access to Tableau Prep. Thank you for your help.

Answer 1

try:

=INDEX(QUERY(IFERROR(SPLIT(FLATTEN(A2:A&"×"&B2:B&"×"&
 REGEXEXTRACT(C1:E1, "^.+? ")&"×"&C2:E), "×")), "where Col4 is not null"))

Answer 2

={A1:B1,"Hours Worked";ArrayFormula(split(flatten(A2&"❄️"&B2&"❄️"&C2:E2),"❄️"))}

demo