Expand my Community achievements bar.

Date Format formatting: Struggling with formatting dates on concatenated field

Avatar

Level 6

We are building a custom form that has a date as part of the input. The inputs then derive a unique identifier for the piece in question.

This is the expression so far, bolded text is the challenge right now

CONCAT(Short Name,"-",Assigned Number,"-",DC, Client Type,"-",Client Product Acronym,"-", DAYOFMONTH(Date Of Meeting),MONTH(Date Of Meeting), YEAR(Date Of Meeting))

I want the results to be -DDMMYY. so 21Jan22 for instance or 15Jun23.

I can see the values I need (DD, MM, and YY respectively), but where do they go in the expression to apply the correct formatting?

4 Replies

Avatar

Community Advisor

I could be wrong but I think you have to bold those in their own column and then sharecol them in, or possibly open and close bold tags in separate columns. It's much like what this guy was trying to do: https://one.workfront.com/s/question/0D54X000071jjuLSAQ/formatting-combined-columns-in-text-mode-so-... .

Avatar

Level 6

Sorry, my question probably wasn't clear: I want to take the text I bolded above and create the format required. I just can't get DDMMYY as a visual output. For instance, right now Feb 2, 2022 looks like "222022" instead of "2Feb22".

Avatar

Community Advisor

ohh, ok. You might check https://one.workfront.com/s/document-item?bundleId=the-new-workfront-experience&topicId=Content%2FRe... ‚Äî not sure if it’s going to ultimately do what you want but I once saw Randy and the rest of the rat pack finagle something very similar using CASE.

Avatar

Community Advisor

And here is the result:

CONCAT(CASE(DAYOFWEEK(CLEARTIME(Planned Completion Date)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),", ",DAYOFMONTH(CLEARTIME(Planned Completion Date)),", ",CASE(MONTH(CLEARTIME(Planned Completion Date)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),", ",YEAR(CLEARTIME(Planned Completion Date)))

Don't forget CLEARTIME, this ignores the time a task is due. It's necessary because it could push to the next day depending on schedule, weekend, holiday, etc. Youl could end up with February 30th since each field is autonomous.

CASE takes a resulting month or day integer and maps it to the included array. eg., 3=March, 3=Tuesday, etc.