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?

  • Try with NOW()-Sdate__c Dec 4, 2012 at 7:13
  • I believe difference should be of return type number only.SO just try with NOW()-Sdate__c Dec 4, 2012 at 7:14
  • @MohithKumar This doesnt work.. the only difference is that error message refers to Datetime instead of Date
    – Prady
    Dec 4, 2012 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 Dec 4, 2012 at 9:48
  • I am not sure why... Today()-LastactivityDate works
    – Prady
    Dec 4, 2012 at 9:55

2 Answers 2



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))
  • this worked, there was a ')' missing after 30*(month(today(). Strangely Today()-LastactivityDate works.
    – Prady
    Dec 4, 2012 at 9:53

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?

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.