Calculated Fields: substitute a date value with a text

In this post i want to show how you can use calculated fields to retrieve number of days between two dates and change result value with texts like: “Today”, “Tomorrow” and “Yesterday”.

1- Create a custom list and set a name for it. In this case I’ve named it “Date Show” .

2- Create a Date Field. In this post it’s named “Date”.

3- Create a Calculated field and enter following formula(I’ve named it “Result”) :

=IF(DAYS360([Date],[Created])=-1,”Tomorrow”,

IF(DAYS360([Date],[Created])=1,

“Yesterday”,IF(DAYS360([Date],[Created])=0,”Today”,

CONCATENATE(TEXT(DAYS360([Date],[Created]),”0″),” Days”))))

4- Now you can enter “New Item” to get results.

date.jpg

Analyzing Formula

Now we are going to take a deep look inside of this long formula to know what it realy does.

There are four function in this formula: IF, DAYS360, CONCATENATE, TEXT

IF formula checks weather a conditions is met.In this scenario I used nested IF functions to check three state of results. Days360 returns number of days between two dates. This function calculate number of days between date field I created in list and “Created” field in custom list. If value equals “0″ Result will replace with “Today”. If it equals “-1″ it will replace with “Yesterday” and if value equals “1″ it will replace with “Tomorrow”.

CONCATENATE function joins two or more texts together and TEXT function transforms a numeric value to text. So the result of last Days360 function will turn to a text and then will join with ” Days” phrase.

You can change phrase with whatever you want.