Expand my Community achievements bar.

SOLVED

Date format in a calculated form field

Avatar

Level 4

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,
1 Accepted Solution

Avatar

Correct answer by
Level 4

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

View solution in original post

9 Replies

Avatar

Level 7

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

Avatar

Level 4

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.

 

Avatar

Level 7

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.

Avatar

Level 4

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!

Avatar

Level 7

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)))

Avatar

Level 4

That worked - thank you so much!

Avatar

Level 4

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!

Avatar

Correct answer by
Level 4

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

Avatar

Level 4

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