SharePoint 2010 Calculated Column Formula for Two Date Fields

=IF(AND([Date1]="",[Date2]=""),"",IF(AND(NOT([Date1]=""),[Date2]=""),TEXT([Date1],"MM/dd/yyyy"),IF(AND([Date1]="",NOT([Date2]="")),TEXT([Date2],"MM/dd/yyyy"),IF([Date1]=[Date2],TEXT([Date1],"MM/dd/yyyy"),TEXT([Date1],"MM/dd/yyyy") & " to " & TEXT([Date2],"MM/dd/yyyy"))))
Scenarios this formula addresses when you have 2 date columns:
  1. When both are empty, you would not like to show anything
  2. When one is available and another is empty, you would like to show only that available date
  3. When both are available and are different, you need to show Date 1 to Date 2
  4. When both are same, only one date must be displayed
A good mix of inbuilt functions available such as IF, AND, NOT and TEXT!


Popular posts from this blog

Using External Content Types with Stored Procedures with Input Parameters

HTTPS Site InfoPath Issue : You do not have permissions to access a web service that provides data required for this form to function correctly

Windows 7 Blue Screen because of ConnectiFy