3

I am pretty basic user of SharePoint 2013 and I have this problem: I have to create a calculated column with two dates in them to look like this:

From 21/2/2016 to 5/3/2016

but whatever I try, the dates are always displayed in a numeric string instead of regular date. I tried a lot of times to put TEXT in front of the column in the formula but I always get errors, no matter what combination of () "" && I use, there is always error. Help please!

1

This would be a little cleaner, tested in SPO:

=CONCATENATE("From "&TEXT([Created], "dd/mm/yyyy")&" to "&TEXT([Modified], "dd/mm/yyyy"))

Yields:

From 12/10/2015 to 12/10/2015

or this to not have the leading zeroes:

=CONCATENATE("From "&TEXT([Created], "d/m/yyyy")&" to "&TEXT([Modified], "d/m/yyyy"))

Yields:

From 17/6/2015 to 18/2/2016

  • Note that the CONCATENATE function is for concatenating all sort of types. Since in this case they are all String (and & is a string concatenation) you can do with: "From " & TEXT([Created], "d/m/yyyy") & " to " & TEXT([Modified], "d/m/yyyy") actually this CONCATENATE function only has 1 parameter, so does nothing – Danny '365CSI' Engelman Feb 18 '16 at 19:30
  • This is just how I am accustomed to writing concatenated calculated columns,stemming from the 2003 days when calculated column documentation was sparse and most functions were prototyped in Excel – Eric Alexander Feb 18 '16 at 19:36
  • Oh it works, but its the & doing all the concat, and then the concatenate function gets 1 string as parameter.. does its work.. and outputs (the same) 1 string.. 2003?.. those were the days.. I can just recall the joyfull day I learned about the [Me] filter.. (and put 35000 records from an Orcale DB into SharePoint.. every night.. with a VBS script running for 5 hours 🙈 – Danny '365CSI' Engelman Feb 18 '16 at 19:40
  • thanks a lot! I can also see now, where I made a mistake....or mistakes, to be correct :D – Ema K. Feb 22 '16 at 12:27
1

Use the following formula:

=("From " & DAY(Date1) & "/" & MONTH(Date1) & "/" &YEAR(Date1))
 &" to "
 & (DAY(Date2) & "/" & MONTH(Date2) & "/" & YEAR(Date2)
  • I only reformatted the Formula (SP ignores spaces and linebreaks when you paste this).. This Formula will indeed produce the same result as TEXT only remember to add one more ) at the end ... or ... leave out the unnecessary ( ) – Danny '365CSI' Engelman Feb 18 '16 at 19:44
0

You can try this if you want mm/dd/yyyy-mm/dd/yyyy as a result.

=CONCATENATE(""&TEXT([Start Date], "mm/dd/yyyy")&"-"&TEXT([End Date], "mm/dd/yyyy"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.