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

Question

I have this formula:

NOW()  -  Lead_Escalation_from_Production__c

And this is what the Lead_Escalation_from_Production__c field holds (our business hours):

IF(MOD(DATEVALUE(CreatedDate) - DATE(1900,1,7),7) = 6, 
DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate + 2)) & " 12:00:00"), 

IF(MOD(DATEVALUE(CreatedDate) - DATE(1900,1,7),7) = 0, 
DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate + 1)) & " 12:00:00"), 

IF(MOD(DATEVALUE(CreatedDate) - DATE(1900,1,7),7) = 5 && CreatedDate > DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 22:00:00"), 
DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate + 3)) & " 12:00:00"), 

IF(CreatedDate > DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 22:00:00"), 
DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate + 1)) & " 12:00:00"), 

IF(CreatedDate < DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 12:00:00"), 
DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 12:00:00"), 

CreatedDate)))))

I am using a workflow rule that if the lead status is not open or has been converted i want it to do the formula above,NOW() - Lead_Escalation_from_Production__c, but I get the error message:Error: Formula result is data type (Number), incompatible with expected data type (Date/Time)

I would like to use now since it will get a closer time

Any suggestions?

Answer 1

If you're trying to add a day, I suspect that's why you're getting the above error. In this part of your formula:

DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate + 1))

CreatedDate +1 results in a number. You want to get the day(CreatedDate) or Month(CreatedDate) or Year(CreatedDate) before adding a number to it. You also want to remove the TEXT from this because you're returning TEXT Values, not numerical values that you do addition with!