Discussions

 View Only
  • 1.  Report Field - Formula Date and concatenate.

    Posted 08-11-2022 12:18
    Edited by Ben Dellsperger 08-11-2022 14:13
    I am working on a report that is used to build certificates for classes that we teach.  The report is exported and then used to mail merge in our certificates.

    We teach classes that are either 1 day or multiple days / weeks.  In the 1 day class, we would like the certificate to just display the date that the class was taken.  In the case where the class spans multiple days, we would like the same field to show the extent of the class.

    I have created a report that grabs all the necessary data for the merge, and have created a report field of type formula-date and am attempting to concatenate within the report. We are using start date and end date as with most projects, but attempting the following.

    If([Project - Start Date]=[Project - End Date],[Project - Start Date],[Project - Start Date]&" - "&[Project - End Date]) 

    As expected, i'm getting the dreaded "expecting date but found text" error.  Any advice on how to resolve this?

    ------------------------------
    Ben 
    ------------------------------


  • 2.  RE: Report Field - Formula Date and concatenate.

    Posted 08-11-2022 13:16
    If you're attempting to concatenate two dates to display on your report, the report formula needs to be Formula-Text, not Formula-Date. You're getting that error because this: &" - "& is a function for text, not dates.

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 3.  RE: Report Field - Formula Date and concatenate.

    Posted 08-11-2022 14:13
    Edited by Ben Dellsperger 08-11-2022 14:14
    This worked, but i also had to convert the start and end dates to text.  FOr anyone else that may need this in the future, i adjusted the type to formula - text and did the following:

    If(
    ToText([Project - Start Date])=ToText([Project - End Date]),NameOfMonth([Project - Start Date])&" "&Day([Project - Start Date])&", "&Year([Project - Start Date]),
    Month([Project - Start Date])!=Month([Project - End Date]),NameOfMonth([Project - Start Date])&" "&Day([Project - Start Date])&" - "&NameOfMonth([Project - End Date])&" "&Day([Project - End Date])&", "&Year([Project - Start Date]),
    NameOfMonth([Project - Start Date])&" "&Day([Project - Start Date])&" - "&Day([Project - End Date])&", "&Year([Project - Start Date]))

    ------------------------------
    Ben Dellsperger
    ------------------------------