1

The business wants two dates to show up in one field in a SharePoint 2013 List.

I have created a calculated column which combines the two dates but I run into issues if one of the dates isn't filled out. I can get a return of "NA" if one or both of the date fields is completed but I can't get it to display if only one of the dates is completed.

Here is what I have so far.

=IF(ISBLANK([Audit Date 2]),"NA",IF(ISBLANK([Audit Date 1]),"NA",TEXT([Audit Date 1],"mm/dd/yy")&" & "&TEXT([Audit Date 2],"mm/dd/yy")))

Thank you!

0

Flip it around a bit - this is pseudocode obviously. If you find a blank don't do a NA immediately, do the test on the next field.If one of the date fields is more important than the other, you can test that field first.

If(isblank(field1),X,TextField1)

Where X would be your test for blank field 2

If(isblank(field2),"NA",TextField2)
0

I think you missed something in the desired results description: "I can get a return of "NA" if one or both of the date fields is completed but I can't get it to display if only one of the dates is completed."

You formula creates this:

enter image description here

Is this what you wanted?

enter image description here

If so, try this formula:

=IF(
  and(NOT(ISBLANK([Audit Date 1])),ISBLANK([Audit Date 2])),TEXT([Audit Date 1],"mm/dd/yy"),
 IF(
  and(NOT(ISBLANK([Audit Date 2])),ISBLANK([Audit Date 1])),TEXT([Audit Date 2],"mm/dd/yy"),
 IF(
  and(NOT(ISBLANK([Audit Date 1])),NOT(ISBLANK([Audit Date 2]))),
  TEXT([Audit Date 1],"mm/dd/yy")&" & "&TEXT([Audit Date 2],"mm/dd/yy"),"NA")))

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.