Scroll the menu to find all the places to visit
Get Value from the Text Function in Excel
07 Jan 2010I’ve been doing a little bit more work with some spreadsheets recently
and I came across a situation where I needed to work with a date and
carry out a comparison to a string of text. My first attempt fell short
as it wouldn’t get the value I wanted, but Excel’s TEXT() function came
to my rescue.
The issue came about like this. I wanted to take a date in the format “dd/mm/yyyy” in one cell and display it in a short date format of “mmm-yy” in the cell next to it.
e.g. “07/01/2010” in cell A1 and “Jan-10” in the neighbouring cell A2.
My original approach was to set the second cell to pick up the value of
the first. So taking the example above A1 would contain “07/01/2010”
and cell A2 would contain a formula =A1. I then set the formatting on
cell A2 to a custom format “mmm-yy”.
I next wanted to check if cell A2 was equal to “Jan-10”. This was where the issue arose as although cell A2 displays “Jan-10” its value is actually “07/01/2010” … the value in cell A1.
The solution was to set the formatting of the A2 cell back to ‘general’
and then change its formula to… =TEXT(A1,"mmm-yy")
The value of cell A2 is then “Jan-10”.
For me this highlights the difference between the content (what is typed into the cell, i.e. the formula), the value (what the formula evaluates to), and what is displayed (the value can be formatted to change how the user sees it on screen/printed).
