+ Start a Discussion
Lil_Ranger1111Lil_Ranger1111 

DateTime Text Formula Field

Hi,

I have a formula field to pull the created date with the time,  but it is showing the seconds and the z.  How do I get rid of the seconds and z?  I've tried a couple of different ways and can get just the date or just the time, but not both.

TEXT(CreatedDate)


Best Answer chosen by Lil_Ranger1111
Jason Curtis NBSFDGJason Curtis NBSFDG
LEFT(TEXT(CreatedDate), 16) will make "2014-05-26 19:43:17Z" into "2014-05-26 19:43"


All Answers

Vidhyasagaran MuralidharanVidhyasagaran Muralidharan
Check this whether it could be helpful
https://developer.salesforce.com/forums?id=906F00000008uO1IAI
Ramu_SFDCRamu_SFDC
Try something as this LEFT(TEXT(CreatedDate), 8). See if this helps, you might need to change the number of characters as required, I just randomly entered as 8.
Jason Curtis NBSFDGJason Curtis NBSFDG
LEFT(TEXT(CreatedDate), 16) will make "2014-05-26 19:43:17Z" into "2014-05-26 19:43"


This was selected as the best answer
Lil_Ranger1111Lil_Ranger1111
Thanks.  That worked.  Now I have to figure out how to change the formatting to MM-DD-YY
Jason Curtis NBSFDGJason Curtis NBSFDG
Well, you could do the following:
MID(TEXT(CreatedDate),6,2) & "-" & MID(TEXT(CreatedDate),9,2) & "-" & MID(TEXT(CreatedDate),1,4)

That strips out the various characters in the date/time and rearranges them.
Jason Curtis NBSFDGJason Curtis NBSFDG
Another way to do it would be to:
TEXT(MONTH(DATEVALUE(CreatedDate))) & "-" & TEXT(DAY(DATEVALUE(CreatedDate))) & "-" & TEXT(YEAR(DATEVALUE(CreatedDate)))
This will give you 5-26-2014, the other MID option will give you 05-26-2014.
This one might be easier to read if having to reference it.
Also, the DATEVALUE is used because the MONTH, DAY and YEAR functions only work with dates, not date/times. So DATEVALUE converts a date/time to a date.
Mano sfdcMano sfdc

Hi Jason,

Below formula not working...
LEFT(TEXT(CreatedDate), 16)

It shows correct date and showing wrong time.
Date/Time: 1/7/2015 8:56 PM
Formula Output: 2015-01-07 15:26