1

I want to display a blank field in the calculated column if both the reference fields are blank, but SharePoint displays the default date as 30-Dec-1899.

Formula I used in Calculated column:

=IF(ISBLANK([Actual Release Date]),TEXT([Forecast Release Date],"dd-mmm-yyyy"),TEXT([Actual Release Date],"dd-mmm-yyyy"))

Updated requirements:

If actual release is blank then it should consider the forecast release in the calculated column, if both dates are available then it should take the actual release date.

But when both the reference fields are blank, then it should leave the calculated value as blank instead of giving the default date (30-Dec-1899).

  • What if both fields are not blank? – Ganesh Sanap Jul 26 at 13:03
0

Below formula shows will show blank field in calculated column if both date fields are blank otherwise it will show "Both Date Fields are not blank".

=IF(AND(ISBLANK([Actual Release Date]),ISBLANK([Forecast Release Date])),"","Both Date Fields are not blank")

References:

  1. IF function.
  2. AND function.

Update:

Below formula meets all your requirements, please check using this:

=IF(ISBLANK([Actual Release Date]),IF(ISBLANK([Forecast Release Date]),"",TEXT([Forecast Release Date],"dd-mmm-yyyy")),TEXT([Actual Release Date],"dd-mmm-yyyy"))

enter image description here

  • If actual release is blank then it should consider the forecast release in the calculated column, if both dates are available then it should take the actual release date. But when both the reference fields are blank, then it should leave the calculated value as blank instead of giving the default date (30-Dec-1899). I tried using, =IF(AND(ISBLANK([Actual Release Date]),ISBLANK([Forecast Release Date])),"",IF(ISBLANK([Actual Release Date]),TEXT([Forecast Release Date],"dd-mmm-yyyy"),TEXT([Actual Release Date],"dd-mmm-yyyy"))), it works for blank condition but fails on the original requirement. – Deepak Nagaraj Jul 29 at 5:40
  • Try Updated formula from my answer. – Ganesh Sanap Jul 29 at 5:53
  • Hard Luck!! :( Still not working for me on the original requirement, but blank condition is working as expected. – Deepak Nagaraj Jul 29 at 6:12
  • What exactly is not working for you? I guess all the requirements you mentioned in above comments are getting fulfilled. – Ganesh Sanap Jul 29 at 6:24
  • If both fields are blank then result is blank. If both fields are not blank then it is taking Actual release date as a result. If Actual date is blank then it is taking Forecast release date as a result otherwise it is taking as Actual release date as a result. – Ganesh Sanap Jul 29 at 6:28
0

Do a search on "excel if and formula" . Here is an example from exceljet.com

=IF(AND(A1="this",B1="that"),"x","")
0

Ganesh's Formula is correct and you can change the "Both Date Fields" to your required text. enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.