Forum Discussion

BenDellsperger's avatar
BenDellsperger
Qrew Trainee
3 years ago

Report Field - Formula Date and concatenate.

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 
------------------------------
  • AhuvaBrown's avatar
    AhuvaBrown
    Qrew Cadet
    3 years ago
    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
    ------------------------------
    • BenDellsperger's avatar
      BenDellsperger
      Qrew Trainee
      3 years ago
      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
      ------------------------------