Tell me more ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.
up vote 1 down vote favorite

I wanted to have a formula field with difference between a datefield and todays date. The formula field is a number to store the no of days.

The formula that i have is 

TODAY() - SDate__c

SDate__c is a Date field.

But when i check syntax it results in

Error: Formula result is data type (Date), incompatible with expected data type (Number).

What am i doing wrong here? OR

How do i get the date difference between todays date and sdate?

share|improve this question
Try with NOW()-Sdate__c – Mohith Kumar Dec 4 '12 at 7:13
I believe difference should be of return type number only.SO just try with NOW()-Sdate__c – Mohith Kumar Dec 4 '12 at 7:14
@MohithKumar This doesnt work.. the only difference is that error message refers to Datetime instead of Date – Prady Dec 4 '12 at 9:44
I am kind of surprised since in my org i have difference of two dates and return type i have no and it displays me the duration in no of days – Mohith Kumar Dec 4 '12 at 9:48
I am not sure why... Today()-LastactivityDate works – Prady Dec 4 '12 at 9:55

2 Answers

up vote 1 down vote accepted

EDIT :

My bad, it does actually let me create a formula field if both fields are of type date, so TODAY() - Date_Field__c in a Number formula field seems to compile okay. Check that your date field is not actually DateTime ? And that your Formula field is of type number.

I don't believe you can use mathematical operators on date fields.

You will need to write something like

365*(Year(today()) - year(sdate__c)) + 30*(month(today() - month(sdate__c))) + (day(today())- day(sdate__c))
share|improve this answer
this worked, there was a ')' missing after 30*(month(today(). Strangely Today()-LastactivityDate works. – Prady Dec 4 '12 at 9:53
up vote 2 down vote

Can you go back and check your field definitions - is sDate__c definitely a date and your formula is definitely a number formula?
Field definitions


I defined a couple of fields and I was able to use the formula as you would expect TODAY() - initial_date__c
enter image description here


Otherwise surely you are in a world of trouble re-inventing the wheel trying to cope with leap years and month lengths?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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