+ Start a Discussion
ArmanMArmanM 

Date/Time Formula field

Hi,

I am trying to add timestamp to a formula field that was a Date type field before but now I am trying to switch this to a Date/Time field. However I am getting an error after changing the formula type from Date to Date/Time. 

Field Name - Completed Date
Error that I am getting - "Formula result is data type (Date), incompatible with expected data type (Date/Time). (Related field: Formula)"
Formula I am using - "IF(ISPICKVAL( Data_Status__c,"Completed" ),TODAY(), NULL)"

I tried replacing Today() function with Now() but thats giving a dynamic timestamp not the time according to the formula which says if the status is changed to 'Completed', add the date and time. 

Why am getting this error ? And How can I add timestamp to this formula field using Date/Time type.

Thanks
Best Answer chosen by ArmanM
SalesFORCE_enFORCErSalesFORCE_enFORCEr
Formula field gets evaluated on every refresh and as per your formula, the timestamp will be populated with NOW() if Data_Status__c is completed so unless you change this field value to something else, the timestamp will keep on updating to NOW. So, what you need is to timestamp only when the Data_Status__c is changed to Completed. Alas, you can;t use ISCHANGED in formula fields so you have to change the data type from formula to date time and then write a workflow rule which will be triggered when Data_Status__c is changed and the value is Completed then add a field update to populate this field with NOW().
Hope this helps.

All Answers

Prateek Singh SengarPrateek Singh Sengar
IF(ISPICKVAL( Data_Status__c,"Completed" ),NOW(), NULL) should work if you have changed the datatype of formula field to DateTime. 
If you formula data type is date, the formula should be IF(ISPICKVAL( Data_Status__c,"Completed" ),TODAY(), NULL)
If its date time the formula should be IF(ISPICKVAL( Data_Status__c,"Completed" ),NOW(), NULL)

I am not sure what you meant by 
"I tried replacing Today() function with Now() but thats giving a dynamic timestamp not the time according to the formula which says if the status is changed to 'Completed', add the date and time. "
Can you explain bit more.
 
SalesFORCE_enFORCErSalesFORCE_enFORCEr
Formula field gets evaluated on every refresh and as per your formula, the timestamp will be populated with NOW() if Data_Status__c is completed so unless you change this field value to something else, the timestamp will keep on updating to NOW. So, what you need is to timestamp only when the Data_Status__c is changed to Completed. Alas, you can;t use ISCHANGED in formula fields so you have to change the data type from formula to date time and then write a workflow rule which will be triggered when Data_Status__c is changed and the value is Completed then add a field update to populate this field with NOW().
Hope this helps.
This was selected as the best answer
ArmanMArmanM
Thanks prateek for answering, what I meant by that is when I replace TODAY() with NOW() the field updates its time everytime I refresh the time not to the time when Data_Status__c  is changed to completed.
However I think the second answer by SalesFORCE_enFORCEr might end up working using workflow rule instead of the formula.