locked
lookup to a concatenated field with a date RRS feed

  • Question

  • Hi

    I have a list that has a lookup field in it that points to a concatenated field where the concatenated field has three fields in it, one of which is a Sharepoint standard date field (MM/DD/YYYY). The date that is displaying in the lookup field is not displaying properly. It is showing a series of numbers that have no relation to the date value (i.e. 40829) and actually both records in this list that display this lookup field both have the same value in it.

    Is there a way to get this lookup field to display something that represents that actual date from the concatenated field?

    Paul

     

    Thursday, October 13, 2011 1:29 PM

Answers

  • You only need to use Text function for values that are not string type

    Its better to make the formula in iterative mode

    First try and render each part individually

    =TEXT([Start Date],"ddmmyyyy")

    =[Country]

    =[Initial/Intermediate Outcome]

    Change till you get proper text and then finally combine them.

    Try this

    =CONCATENATE(TEXT([Start Date],"ddmmyyyy"),"-",[Country],"-",[Initial/Intermediate Outcome])



    Shubham Goyal
    • Marked as answer by Paul-NYS Friday, October 14, 2011 5:38 PM
    Friday, October 14, 2011 12:49 PM

All replies

  • I am not able to get your question. Lookup field should show proper value.

    How are you concatinating the date fileds?

    Make calculated column with formula like this 

    =CONCATENATE(TEXT(Created,"ddmmyyyy"),"-",TEXT(Modified,"ddmmyyyy"))

    And now reference the above calculated column in a lookup field. Value will be displayed properly


    Shubham Goyal
    Thursday, October 13, 2011 7:58 PM
  • This is what I was using:

    =[Start Date]&(" - ")&County&(" - ")&[Initial/Intermediate Outcome]

    Start date is the problem.

    Friday, October 14, 2011 12:35 PM
  • I agree with Shubham.  Try the following:

    =CONCATENATE(TEXT([Start Date], "ddmmyyyy"), " - ", [County], " - ", [Initial/Intermediate Outcome])

     


    - Xenox G.
    Friday, October 14, 2011 12:40 PM
  • Use the text and concatenate formula as shown above and let us know if your problem is resolved
    Shubham Goyal
    Friday, October 14, 2011 12:41 PM
  • I tried to use your formula above and incorporate my other fields using several different variations, but I am getting syntax errors:

     

    =CONCATENATE(TEXT(Start Date,"ddmmyyyy"),"-", TEXT(County), "-", TEXT(Initial/Intermediate Outcome))

    Friday, October 14, 2011 12:42 PM
  • You only need to use Text function for values that are not string type

    Its better to make the formula in iterative mode

    First try and render each part individually

    =TEXT([Start Date],"ddmmyyyy")

    =[Country]

    =[Initial/Intermediate Outcome]

    Change till you get proper text and then finally combine them.

    Try this

    =CONCATENATE(TEXT([Start Date],"ddmmyyyy"),"-",[Country],"-",[Initial/Intermediate Outcome])



    Shubham Goyal
    • Marked as answer by Paul-NYS Friday, October 14, 2011 5:38 PM
    Friday, October 14, 2011 12:49 PM
  • Thanks Shubham. After doing it piece by piece like you did above, I was able to get it to work with something very similar to the above.

    Paul

     

     

    Friday, October 14, 2011 5:30 PM
  • It looks like you really meant 'Country' instead of 'County'. 

    That may be why the other forumula didn't work.


    - Xenox G.
    Friday, October 14, 2011 6:11 PM