Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Convert a date to a number

Topic Labels: Formulas
Solved
Jump to Solution
1671 10
cancel
Showing results for 
Search instead for 
Did you mean: 

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”

1 Solution

Accepted Solutions

Hi @Kelly_Brask,

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

DATETIME_FORMAT(
   {Date Field},
   'YYMMDD'
)

See Solution in Thread

10 Replies 10

Hi @Kelly_Brask,

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

DATETIME_FORMAT(
   {Date Field},
   'YYMMDD'
)

This worked perfectly, thank you so much @Jeremy_Oglesby!

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.

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.

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

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.

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.

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.

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!

Ah, I’m just a guy. I’m no conference speaker or tutorial producer.