Issue with cell referencing

I am working on a project dashboard in MS Excel. I needed to use the text function, but there is an issue; the original cell value doesn't change into the format cell specified and returns the value...