LO Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 4; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: CL threaded
How can I make =TEXT(2018-4-6,"nnn") display Fri instead of Friday, Mon instead of Monday etc etc
=LEFT( TEXT(2018-4-6;"nnn"); 3)
Thank you very much!
Sorry your initial Formula was wrong, it should be:
=TEXT("2018-4-6","nnn")
with quotes around the iso-date
therefor the extended Formula is:
=LEFT( TEXT("2018-4-6";"nnn"); 3)
Actually it works without the quotes. My actual application is a cell reference instead of the date itself but your initial reply without the quotes worked fine when I pasted it to a sheet to test it before replying.
No it doesnt, because it calculates 2018-4-6 which is 2008 which is as Date Day 2008 since LO-epoch 1899-12-30 → 1905-06-30
See the answer by @erAck.
In addition:
Different locales may use different letters for format codes.
As an alternative to the D codes based on the English word “day” like in DDD you can use the “N”.
N seems to be treated as a literal.NN denotes for the name of the weekday given by the abbreviation specific for the locale.NNN denotes for the full name of the day.NNNN appends a comma if the locale expects one in the position.The TEXT() function though tries hard to support both, the locale’s localized keywords (if any, few locales actually suffer from that legacy), and English keywords. The NN/NNN/NNNN keywords are an OpenOffice/LibreOffice peculiarity, if used with TEXT() and loaded in other spreadsheet implementations like Excel or Gnumeric it doesn’t work and will return an error.
Thanks for the info!
First, you don’t want to calculate 2018-4-6 (=2008) but a date value instead, so either the text string "2018-4-6" or better DATE(2018;4;6). The serial date number 2008 just happens to fall on a Friday as well as the serial date number 43196 for 2018-04-06.
Then you could use the format code "NN" instead of "NNN" but better (because interoperable with other spreadsheet implementations) would be "DDD".
So:
=TEXT(DATE(2018;4;6);"DDD")
Yes, my actual use is =TEXT(A2,“nnn”) where A2 is a cell in a column with dates formatted YYYY-MM-DD. I just typed 2018-4-6 to make it clear I was talking about a date field.