How to get datetime field in format dd/mm/yyyy hh:mm:ss format using formula field. I have date time as "9/12/2016 5:21 PM" but i want it as "12/9/2016 5:21 PM"

1 Answer 1



When you create a formula field, you would need to set it to return DateTime:

enter image description here

You'd need to explain in a bit more detail as to what your problem might be, but as you can see from the example there, you can use functions such as NOW() which would return the current DateTime.

The format that is returned depends on your Locale Settings.

To find out what date/time format your Locale setting uses:

  1. From your personal settings, enter Advanced User Details in the Quick Find box, then select Advanced User Details. No results? Enter Personal Information in the Quick Find box, then select Personal Information.

  2. View the date/time format used in the read-only Created By field. This is the format you should use for entering dates and times in Salesforce fields.

If something like M/D/Y H:M:S is being returned instead of something like D/M/Y H:M:S and you'd prefer the latter, you can adjust the settings by going to...

Setup > My Personal Information > Personal Information

... And adjusting the Locale setting there.


You could use a formula field to return a String (text) and use the following to construct your preferred DateTime:

MID(TEXT(NOW()), 9, 2) // Day
&"/"& // Add a slash
MID(TEXT(NOW()), 6, 2) // Month
&"/"& // Add a slash
MID(TEXT(NOW()), 0, 4) // Year
&" "& // Add a space
MID(TEXT(NOW()), 12, 5) // Get the whole time (hh:mm)

Basically here we're using the MID method to cut the DateTime based on their start position and how many characters we want to select. This will create DD/MM/YYYY HH:MM. Don't copy the comments into the formula.

  • i need it for email template. So i am using one datetime field but it is showing me as standard salesforce format mm/dd/yyyy hh:mm:ss AM/PM. But i need it in dd/mm/yyyy hh:mm:ss AM/PM so i am thinking to create a formula field for that.
    – Jack vardy
    Commented Dec 22, 2016 at 9:59
  • @Jackvardy See my edits.
    – Dan Jones
    Commented Dec 22, 2016 at 10:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .