2

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 3

1

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)
1
  • Thank you for your suggestion !! It worked well .. And i was using EST time values so my TZoffset is 0.166 !! Thanks jjbennett
    – AjaySFDC
    Aug 14, 2014 at 11:37
1

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()))
2
  • 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"
    – AjaySFDC
    Aug 13, 2014 at 13:44
  • 1
    You should update your question with that information.
    – BarCotter
    Aug 13, 2014 at 13:50
0

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()))
2
  • My requirement is to use NOW() a DATETIME datatype. I tried using TODAY(), its suppressing my time values for the same.
    – AjaySFDC
    Aug 13, 2014 at 12:43
  • this will return 1-3-2014 rather than 01-03-2014
    – cropredy
    Aug 13, 2014 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.