+ Start a Discussion
Vasavi VajinepalliVasavi Vajinepalli 

Formula to update a date field


I've date field say "End_date". if the value for this field is 12/31/9999, i need to update the value as 12/31/2999. Can anyone please help me in constructing a formula for this.

William TranWilliam Tran
You should look into using data loader( you can use the wizard if you dataset is smaller).  Export all records with 12/31/9999 and import them with 12/31/2999.

Formula cannot update fields.   You can create a new formula field, but is that what you wanted?  

If yes, your formula could be as simple as if(End_date=12/31/9999, 12/31/2999, End_date)

You may need to correct the syntax but the logic is described above.

Vasavi VajinepalliVasavi Vajinepalli
Hi William,

Thanks for quick response. When i try to use the above formula, am getting " Error: Incorrect parameter type for operator '='. Expected Date, received Number". And is there any way we can use workflow rule to achieve this.

anto nirmalanto nirmal
Hi Vasavi, 
I assume your source system is trying to update salesforce with the date value 
12/31/9999, which you want to change as 12/31/2999, which is compatible to salesforce.

This can not be done in workflow rules since they execute way after the record is saved.
This logic needs to be handled in the step before the salesforce validation.
You can write the logic in before update/insert step to check if the incoming value is 12/31/9999,
and then change the value to 12/31/2999.

Anto Nirmal

Luison Lassala 13Luison Lassala 13
Hi Vasavi,

This might sound like a roundabout way of solving your problem, but if you think you are likely to have to update many different field values all at once I would recommend a little app from the AppExchange called "ActionGrid". You can filter views by any field for whichever field value you wish and then execute a Batch update on the relevant field with a new value. It works seemlessly and in seconds. It's wonderful!