Ask Search:
Mike DeMilleMike DeMille 

Help with a number/text formula field

I'm using a formula field to add a '0' before the date numbers 1-9 to essentially make a 2-digit date field for all dates.  So January would read '01'...

I'm referencing another field that returns simply a number for the date (1,2,3,4,5....etc) and I want it to add a '0' before any single digit month..  Here is what I have, but I'm getting errors.  Im using a formula/NUMBER field type here.

IF(  Recurring_Month_Calc__c =1,01,
IF(  Recurring_Month_Calc__c =2,02,
IF(  Recurring_Month_Calc__c =3,03,
IF(  Recurring_Month_Calc__c =4,04,
IF(  Recurring_Month_Calc__c =5,05,
IF(  Recurring_Month_Calc__c =6,06,
IF(  Recurring_Month_Calc__c =7,07,
IF(  Recurring_Month_Calc__c =8,08,
IF(  Recurring_Month_Calc__c =9,09,
Recurring_Month_Calc__c)))))))))

any suggestions on whether I need to use quotes, or the use of TEXT function?
Best Answer chosen by Mike DeMille
Mayank SrivastavaMayank Srivastava
What's the data type of the Recurring_Month_Calc__c field?
If it's number , ue the following:
LPAD(TEXT(Recurring_Month_Calc__c) ,2,'0')
The formula field should be of type Text.

All Answers

Mayank SrivastavaMayank Srivastava
What's the data type of the Recurring_Month_Calc__c field?
If it's number , ue the following:
LPAD(TEXT(Recurring_Month_Calc__c) ,2,'0')
The formula field should be of type Text.
This was selected as the best answer
Mark ErdeljacMark Erdeljac
Mike,

I am not sure I completely understand your question but I believe if the new field you are using is a Text Formula Field you can use the following formula to get a two digit month:
IF( MONTH(TODAY()) <= 9, 
'0' + TEXT(MONTH(TODAY())), 
TEXT(MONTH(TODAY())) 
)

Since I don't have a pre-existing 1,2,3,4...etc field I just used the pre-built month and today's date stuff to check and make sure my logic would work. Also according to the Salesforce documentation on Custom Field Types (https://help.salesforce.com/apex/HTViewHelpDoc?id=custom_field_types.htm&language=en_US), it claims the number type is "treated as a real number and any leading zero's are stripped" this would apply to formula's that return number types as well. Again I apologize if this doesn't help you and I misunderstood but at least thought I would try and help out, hope that I could.


Sincerely,

Mark E.
Mike DeMilleMike DeMille
Thanks to you both for your help and sorry for the confusing explanation.  Mayank, thank you very much for the easy suggestion.  I had never used the LPAD trick before and it did the trick.