Text()¶
One of the simplest and important formula for doing the formatting on the fly is formula text() Syntax is text(cell reference,*cell format*)
Now cell reference would be the reference cell, like A1, or B1 depending on the cell in which your original data is, cell format is interesting function.
What we do in CellFormat, or Ctrl+1 shortcut)
Date formatting¶
How may of us have faced the problem of having our input / upload file failed because the data is not formatted as per the database / target system requirements. e.g. If your locale / computer is United States language based, then the dates would be in mm-dd-yyyy format, while if it is United Kingdom based then it would be dd-mm-yyyy format. So 01-31-00, yes it seems would definitely be 31st January 2000, but is it, for Excel, it may be 31st January 2000 or it may also be a Text, as it did not recognize it as a number. Or wait, did we mean it to be 31st January 1900??
One more similar scenario, 01-02-2000, this may be well 2 nd January 2000, or 1 st February 2000, as per system behaviour. Well that’s the “Blessing” as well as “Curse” of auto-formatting and auto-detection by Excel.
But yes there is an option that we have, text formatting! If you want the output value to be in the dd-mm-yyyy format, then just use this option:
=text('cellreference',"dd-mm-yyyy")
No matter in what way you have the dates mentioned, e.g. if it is in say 1-Feb-2000, the output would be 01-02-2000
Formatting Examples¶
Example how 1st Feb 2000 would be shown in different formatting examples.
Format | Output | Remarks |
|---|---|---|
| 01-02-00 | Note the leading 0 in dates and month |
| 1-2-2000 | Note the leading 0 are gone |
| 02.01.2000 | Note the separator and month first |
| 01-Feb-2000 | mmm corresponds to month in text form |
| Tue 01-02 | ddd corresponds to the Day we may not be needing this for data upload activity, but good to know right? |
| 20000201 | Easy way to get an unique ascending number for every day. |