3

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?

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

3 Answers 3

3

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

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?

0

I received this error because I was trying to subtract two dates into a date formula field. Just needed to change it to number.

Posting in case that saves anyone some small headache.

1
  • 1
    date - date = number, and date +/- number = date
    – Derek F
    Commented Mar 6 at 2:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .