Wednesday, July 1, 2009

Remove #NUM from the list data difference

To remove the #NUM from the resilt of taking difference between two sharepoint column values.

Here is a scenario :

There are two dates , date1 and date 2

date 2 - date1 will give result if date 2 is after date1, if not it will give the result #NUM

This can be resolve by using this formula :

=IF(ISERROR(DATEDIF(Date1,Date2,"d")), -DATEDIF(Date1,Date2,"d"))

If the difference between the two dates is negative ( ie, Date1 - Date2 = -ve value ) then it will return -(Date2-Date1)

for eg : 2008/02/02 - 2008/02/01 will give 1 as value, but 2008/02/01 - 2008/02/02 will give #NUM as result normally, but the above calculated will give -1 as result.