Date Format formatting: Struggling with formatting dates on concatenated field | Community
Skip to main content
R
Ryan_McGee
Level 5
February 11, 2022
Question

Date Format formatting: Struggling with formatting dates on concatenated field

  • February 11, 2022
  • 2 replies
  • 796 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

2 replies

skyehansen
Community Advisor
skyehansenCommunity Advisor
Community Advisor
February 12, 2022

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-close-to-the-right-answer .

R
Ryan_McGeeAuthor
Level 5
February 14, 2022

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".

skyehansen
Community Advisor
skyehansenCommunity Advisor
Community Advisor
February 14, 2022

ohh, ok. You might check https://one.workfront.com/s/document-item?bundleId=the-new-workfront-experience&topicId=Content%2FReports_and_Dashboards%2FReports%2FCalc_Cstm_Data-Reports%2Fcalculated-data-expressions.html&_LANG=enus ‚Äî 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.

RandyRoberts
Community Advisor
RandyRobertsCommunity Advisor
Community Advisor
February 14, 2022

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.

Terms & ConditionsAccessibility statement

Loading footer...