4

I am trying to create a line break within a calculated column that displays the Date in DD MM YY format. The purpose is to utilize the field in a script which is altering the list view. My calculated column entitled Date contains this:

=TEXT(Created, "dd mm yy")

I know it's possible to render HTML if I change the data type to a Number. However, I don't really need the HTML to render in the list, ideally I just want it to render in plain text like so: 

"DD <br/> MM <br/> YY".

If I add the
within the current code, I get something like: <6r>DD<6r>MM<6r>YY. Not sure why the b is being turned into a 6.

user2004758
  • 396
  • 5
  • 19

1 Answers1

2

Wit your Formula

=TEXT(Created, "dd mm yy")

You provide 2 parameters to the TEXT( ) function,
telling it to format (replace with values) Created with the format: "dd mm yy"

The output is: day month year

"11 12 16"

The TEXT() function was modeled after (but is not the same), the Excel TEXT () Function,
fairly good documented in the Text Function Examples Excel Sheet

So characters in the format string have special meaning

I don't know Why b is converted, but with

=TEXT(Today()-365, "b")
=TEXT(Today(), "b")
=TEXT(Today()+365, "b")

it is fair to say it is the two digit Year

Interesting to note is that y is the same as yy
And the SharePoint TEXT() function does not care about upper/LOWER case

To exclude characters from being formatted you have to escape them with a slash: \

=TEXT( Today() ,"dd mm yyyy \# DDD DD MMMM YYYY")
 &"<br>"
 &TEXT(TODAY(),"\b:b \d:d \h:h \m:m \s:s \y:y")
 &TEXT(TODAY(),"\<\B\R>\B:B \D:D \H:H \M:M \S:S \Y:Y")

Outputs:

b - year (but why?)
d - day
y - year

h- hours = 0 because Today() has no timestamp
m - minutes
s- seconds

To make the <BR> work as a Line Break you have to output the Text as HTML by setting its field output to anything BUT Text (it is a trick)

Number will align the text to the right (because it is supposed to be a Number),
Set it to DateTime to have the text Left Aligned

Note, I used Today() as an example, be aware it does NOT recalculate every day like it does in Excel: The Formula is only updated when the Item changes, so Today() is effectively the same as Modified (without the time stamp!) See https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column/151336?s=1|0.0000#151336

Most basic Excel functions are the same in SharePoint,
see:

https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79
  • Thanks for the details, however this didn't really answer my question. I already know all of this. I indicated I currently have my Date column with a Number data type. I don't need it parsed as HTML, I just need it parsed as plain text with the date formatted as I indicated above. Adding a forward slash still renders the weird <16r>. I am looking for the correct syntax [or if it's possible to even do this]. Thanks for your input all the same. – user2004758 Dec 11 '16 at 13:33
  • I explained why you should use a backslash, not a forward slash: =TEXT(Created,"dd <\br/> mm <\br/> yy"). If you want to render the
    as text you have to set it to Single Line of Text, as anything else will make the Browser interpret it as HTML     – Danny '365CSI' Engelman Dec 11 '16 at 13:40
  • Ah, sorry. I think got overwhelmed by all the examples. This works perfectly, thank you! – user2004758 Dec 11 '16 at 13:46
  • Cool, mark it as answer so you gain more StackOverlfow priviledges, an onto your next question. – Danny '365CSI' Engelman Dec 11 '16 at 13:52