Formulate 'Date Created' into mm/yy

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I want a field that reflects the date a record was created without the year and in a '01/01' format.

Thank you!

Photo of Amber

Amber

  • 506 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
You can create a Formula Text field, with the following formula:

var Text MMYYDatewithHyphen = Right(ToFormattedText(ToDate([Date Created]), "DDMMYY"), 5);

List("/", Part($MMYYDatewithHyphen, 1, "-"), Part($MMYYDatewithHyphen, 2, "-") )


First, the "ToDate([Date Created])" converts the [Date Created] field from a Date/Time field to a Date.

Then ToFormattedText converts to a DDMMYY format - such as "12-05-16".

The Right function then takes the rightmost 5 characters - resulting in "05-16".

Now the Part function returns the appropriate part of the text, when separated by the delimiter character "-".

So Part 1 = "05", and Part 2 = "16".

Finally use the List function to string these text parts together, separated by "/".
Photo of Amber

Amber

  • 506 Points 500 badge 2x thumb
Xavier, thanks for the solution!

I removed the ToDate part after changing my field to have just the date and not the time.

I'm getting the following error: A variable declaration must end with a semi-colon.

Also, is there a way to have a days and months appear as 7 vs 07.

Thank you!
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Can you post your current formula?
Photo of Amber

Amber

  • 506 Points 500 badge 2x thumb
Sure:

var text MM = Right(Month([Date]),2);

var text DD = Left(ToText(Day([Date])),2);

$MM &"/"& $DD
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
You were missing the "ToText" around the "Month([Date])" in the first line.

"Month([Date])" returns a number, and you need to convert it into Text.

Here's the updated formula:

var text MM = Right(ToText(Month([Date])),2);

var text DD = Left(ToText(Day([Date])),2);

$MM &"/"& $DD