Date format in a calculated form field | Community
Skip to main content
C
CherylLu
Level 3
April 28, 2023
Solved

Date format in a calculated form field

  • April 28, 2023
  • 8 replies
  • 2286 views

I have a calculated custom field that is pulling multiple pieces of data into one field. The bold field is the one I'm stuck on. The user enters a date in a calendar field, but when I pull the date into this field, I need the date to be MMDDYY rather than 4/27/23. I've looked online but haven't been able to find this situation.

 

CONCAT("LL",{DE:Driver file data source},"_",{DE:ES Project Number system generated},"_",{DE:Project letter for Driver file 1, entered by PM},"_",{DE:Driver file delivery date requested - driver file 1})
 
thanks,
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by The_Real_Melinda_Layten

RIGHT(CONCAT("0",MONTH({DE:Driver file delivery date requested - driver file 1})),2)

RIGHT(CONCAT("0",DAYOFMONTH({DE:Driver file delivery date requested - driver file 1})),2)

 

So X become 0X

And 10 becomes 010 becomes 10 etc

8 replies

_Manish_Singh
_Manish_Singh
Level 9
April 28, 2023

Are you saying it should be 04/27/23 rather than 4/27/23?

C
CherylLuAuthor
Level 3
April 28, 2023

No, I need it to be without the slashes in the date. It doesn't matter if it's 4 or 04 for the month.

 

_Manish_Singh
_Manish_Singh
Level 9
April 28, 2023

I can think of extracting the day, month and year from the desired date field and concatenate it. Something like this:

CONCAT(MONTH({entryDate}),DAYOFMONTH({entryDate}),RIGHT(YEAR({entryDate}),2))

Other community members may have better ideas.
    C
    CherylLuAuthor
    Level 3
    April 28, 2023

    Thanks, I'm not sure where to put that in relation to the field name it's pulling from though  - the bold part here. I've tried inserting it before and after and get errors

     

    CONCAT("LL",{DE:Driver file data source},"_",{DE:ES Project Number system generated},"_",{DE:Project letter for Driver file 1, entered by PM},"_",{DE:Driver file delivery date requested - driver file 1})

     

    Thanks for your help!

    _Manish_Singh
    _Manish_Singh
    Level 9
    April 30, 2023

    Have you tried this..

    CONCAT("LL",{DE:Driver file data source},"_",{DE:ES Project Number system generated},"_",{DE:Project letter for Driver file 1, entered by PM},"_",CONCAT(MONTH({DE:Driver file delivery date requested - driver file 1}),DAYOFMONTH({DE:Driver file delivery date requested - driver file 1}),RIGHT(YEAR({DE:Driver file delivery date requested - driver file 1}),2)))

    C
    CherylLuAuthor
    Level 3
    May 1, 2023

    That worked - thank you so much!

    C
    CherylLuAuthor
    Level 3
    May 1, 2023

    That worked, but now we do need the month and date to be mmdd. I'm unable to figure out how to get a leading zero, if the month or day are only 1 digit numbers. 

    thanks!

    The_Real_Melinda_Layten
    The_Real_Melinda_LaytenAccepted solution
    Level 3
    May 1, 2023

    RIGHT(CONCAT("0",MONTH({DE:Driver file delivery date requested - driver file 1})),2)

    RIGHT(CONCAT("0",DAYOFMONTH({DE:Driver file delivery date requested - driver file 1})),2)

     

    So X become 0X

    And 10 becomes 010 becomes 10 etc

      C
      CherylLuAuthor
      Level 3
      May 2, 2023

      THANK YOU!! I had the right pieces in the wrong order. I really appreciate your help.

      Terms & ConditionsAccessibility statement

      Loading footer...