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).

Improve this question
1
  • What if both fields are not blank?
    – Ganesh Sanap - MVP
    Commented Jul 26, 2019 at 13:03

3 Answers 3

Reset to default
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

Improve this answer
9
  • 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
    Commented Jul 29, 2019 at 5:40
  • Try Updated formula from my answer.
    – Ganesh Sanap - MVP
    Commented Jul 29, 2019 at 5:53
  • Hard Luck!! :( Still not working for me on the original requirement, but blank condition is working as expected.
    – Deepak Nagaraj
    Commented Jul 29, 2019 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 - MVP
    Commented Jul 29, 2019 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 - MVP
    Commented Jul 29, 2019 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","")
Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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