How to automatically convert/transform a formula to its value | General Excel Questions & Answers | Excel Forum

Hello,

I have stumbled onto a template which is having a feature that puzzles me. In this template, in sheet Calendar View, starting from cell C5 to AR5 you see a three letter abbreviation of the weekdays. Nothing strange about that. But as I have Swedish regional setting the formula in the below cells did not show any values, as the abbreviation SUN is SÖN in Swedish. So I typed in a simple formula that would fix this "issue" for me without me having to type in the abbreviations manually.

But as soon as I pressed the enter key I got a 0 instead of the expected SÖN. So I checked and to my surprise the formula was gone, the cell had a 0 and no formula at all. I then created a new sheet and wrote the formula there and this time I got the expected value SÖN, so nothing wrong with the formula. I copied it, returned to cell C5 in Calendar View sheet and pressed Ctrl + V to paste in the formula. Now I got the expected value, but when checking again the formula is gone. The cell contains of SÖN and nothing more.

The formula I typed in cell C5 was taken from a portion of the formula in cell C6, modified it a bit so I could copy the formula to the other cells in row 5.
=UPPER(TEXT(DATE(Calendar_Year;ROW($A1);COLUMN(A$1));"ddd"))

I don't find any VBA code and to my knowledge of Excel this is not possible to do unless done so manually by copying and choosing to paste the value. This is done automatically. But how?
When typing in or pasting the formula when I am in the formula field I get 0 as value. When just clicking on cell C5 and then paste in the formula I get the abbreviation as value. But the formula is gone.

I really want to learn how to do this and I can't find any tip other than the manual copy and paste value solution or using VBA code.

Br,
Anders