[SalesForce] is there anyway to get datetime field in format dd/mm/yyyy hh:mm:ss format using formula field

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"

Best Answer

Yes.

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.

Edit

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.

Related Topic