I have a requirement to convert the DATE FORMAT of NOW() to MM-DD-YYYY in formula editor. By doing using DATETIME.format() i can do it in APEX Classes, but i cannot get the same value in FORMULA EDITOR
3 Answers
There is an idea out on the idea exchange for TIMEVALUE which I recommend voting up here, but until then it looks like you will need to a timezone offset to get the time as a string.
TEXT(MONTH(DATEVALUE(NOW())))+"/" +TEXT(DAY(DATEVALUE(NOW())))+"/" +TEXT(YEAR(DATEVALUE(NOW()))) & " " &
MID(TEXT(NOW()-tzoffset), 12, 2) & ":" &
MID(TEXT(NOW()-tzoffset), 15, 2) & ":" &
MID(TEXT(NOW()-tzoffset ), 18, 2)
-
Thank you for your suggestion !! It worked well .. And i was using EST time values so my TZoffset is 0.166 !! Thanks jjbennett– AjaySFDCAug 14, 2014 at 11:37
You can use the MONTH
, DAY
and YEAR
formula functions to do this as shown below. These functions return an Integer
so you will need to wrap them in TEXT()
to convert them to a String
TEXT(MONTH(DATEVALUE(NOW())))
& '-'
& TEXT(DAY(DATEVALUE(NOW())))
& '-'
& TEXT(YEAR(DATEVALUE(NOW())))
If you use TODAY()
rather than NOW()
you don't need to convert from DateTime
to Date
and the formula gets a little simplier
TEXT(MONTH(TODAY()))
& '-'
& TEXT(DAY(TODAY()))
& '-'
& TEXT(YEAR(TODAY()))
-
When i use TEXT(MONTH(DATEVALUE(NOW()))), this does not return TIME values for the DATETIME field. It just gives only Dateformat as i desire. But i need time value. Something like this , "8-13-2014 19:14:00"– AjaySFDCAug 13, 2014 at 13:44
-
1
if you are just after date, you can use TODAY instead of NOW
give this a try and see if it works for you
TEXT(MONTH(TODAY()) & '-' & DAY(TODAY()) & '-' & YEAR(TODAY()))
-
My requirement is to use NOW() a DATETIME datatype. I tried using TODAY(), its suppressing my time values for the same.– AjaySFDCAug 13, 2014 at 12:43
-