Convert a date to a number

[Kelly_Brask]

Is there a formula where I can convert a date in a date field to a string of numbers? For example, for today (2020-11-20) I would like it to return “201120”

[Jeremy_Oglesby · Accepted solution]

Hi @Kelly_Brask,

You should be able to do this with the DATETIME_FORMAT() function.

DATETIME_FORMAT(
   {Date Field},
   'YYMMDD'
)

[Kelly_Brask]

This worked perfectly, thank you so much @Jeremy_Oglesby!

[kuovonne]

Note that this solution will look like a number but it will actually be a text string. If you need the value to behaving like a number, you will need to wrap it in the VALUE function.

Note that some operations such as sorting can be done on text strings, but other operations such as adding or multiplying need to be done on a number.

[Kelly_Brask]

OK, Kelly, breathe, breathe… Try not to freak out that Kuovonne, of the September virtual conference and other videos fame, just answered your question!!!

Hey Kuovonne, thanks! Yes, I just want to use the date as the invoice number, but appreciate your feedback and have been wanting to learn more about the VALUE function.

[Jeremy_Oglesby]

Kelly_Brask:

OK, Kelly, breathe, breathe… Try not to freak out that Kuovonne, of the September virtual conference and other videos fame, just answered your question!!!

:joy: ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎ ‏‏‎

[kuovonne]

Kelly_Brask:

Try not to freak out that Kuovonne, of the September virtual conference and other videos fame, just answered your question

Oh dear. :blush: Don’t freak out. I’m just a human like anyone else.

I’ve learned a large part of what I know because other people in this community have been generous in sharing their knowledge.

If you want to learn more about VALUE(), it is explained in the formula field reference, as the last entry under Numeric functions.

[Kelly_Brask]

Thanks Kuovonne :slightly_smiling_face: I really admire how you taught yourself so much and so well! You’ve been very inspiring in the videos I’ve seen. That project with the book about plants? Amazing.

[kuovonne]

Thanks. By the way, @Jeremy_Oglesby, who actually answered your initial question, is a total star in this community. He is also one of the people that I have learned a lot from, and he was very welcoming when I first started posting in this community.

[Kelly_Brask]

Absolutely! Did not mean to overlook your quick help @Jeremy_Oglesby :slightly_smiling_face:
I am very impressed with the know-how and generosity of this community. It makes learning more fun!