Calculated field based upon Date and Time field serves up a weird number

Question

I have a list of learning courses. One column is a Date and Time column showing when a learning course will run.

I wanted to use this column to build a unique course ID. However when I set up another column and used the formula [Date] it served up 45,487 for the date 14/07/2024.

Can anyone let me know where I am going wrong?

Answer 1

You will have to use the TEXT() function to convert the date time column in specific format.

Example 1:

Formula:

=TEXT([Date],"mmddyyyy")

Output:

Example 2:

Formula:

=TEXT([Date],"mm-dd-yyyy")

Output:

Note:

1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
2. Use correct display name of your SharePoint columns in above formula.
3. Wrap column names inside [] if your column name has space in it. For example: [Choice Column 5]

Official Documentations:

1. Calculated Field Formulas