Search code examples
excelgoogle-sheetsexcel-formulaexport-to-excelexcel-web-addins

Excel adds "@" to SWITCH and creates #NAME error, when first opened. Why?

I have a google doc spreadsheet, where I calculate name of the weekday based on date. I do it with formula:

=SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")

So the spreadsheet looks like:

enter image description here

I would like to export it into Excel and then send it via outlook and possibly open in WEB Excel app. After Exporting and opening in a WEB Excel, I see that the formula is prepended with a extra @:

=@SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")

Which I thought was the cause of #NAME? error, which is shown in web based outlook and web based excel:

enter image description here

I would like open file in a based WEB Excel app.

UPDATE Following advice from the comments I have unzipped the file and checked the worksheets/sheet.xml. The xml contains the rule without the "@":

<f t="shared" ref="A3:A31" si="1">SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")</f>

So, @ may not be the reason for #NAME? error. How to fix this #NAME? error with this formula, if it is not created by @?

Solution

  • Disclaimer: this is just a workaround to OPs situation & not an intended solution that solves the @ character that gets prepended to switch() when uploaded to excel-web

    An alternative formula (to OPs switch()) which somehow retains ONLY the data (formula vanishes) when downloaded as .xlsx file and uploaded to excel-web

    ={"header";
 index(if(len(B3:B),xlookup(weekday(B3:B),sequence(7),text(sequence(7),"dddd"),),))}

    enter image description here