0

I have a custom list with numerous date columns that feed calculated columns for each section. I need to be able to export this "All Items" view to an excel for reporting. Problem Set: Not every date received and date completed is going to have data at export. The javascript I am running to update all items so that the NOW() function in the calculated columns will update is being applied to all of the # of days pending with XXX. I am trying to figure out how to write the ISBLANK formula to hide data if both the date received and date completed are blank. My current formula is working to hide the data if the date completed is not blank, it will generate a number. I need it to hide the 44,118 if both dates are blank.
=IF(NOT(ISBLANK([FOIA Date Completed])),"",[FOIA Date Received]-TEXT(NOW(),"mm/dd/yyyy")) enter image description here

Improve this question

1 Answer 1

Reset to default
1

You can try this:


=IF(AND(NOT(ISBLANK(ReceivedDate)),NOT(ISBLANK(CompletionDate))),DATEDIF(ReceivedDate,TODAY(),"d"),"")

OR

=IF(AND(NOT(ISBLANK(ReceivedDate)),NOT(ISBLANK(CompletionDate))),TEXT(NOW(),"mm/dd/yyyy")-ReceivedDate,"")

enter image description here

The updated formula

=IF(AND(NOT(ISBLANK(ReceivedDate)),ISBLANK(CompletionDate)),DATEDIF(ReceivedDate,TODAY(),"d"),"")

enter image description here

Improve this answer
5
  • Thank you for your response. The answer you provided does not fully meet my requirements. I need for the # of days pending to be blank if both dates are blank also. I need for it to return a number if the completed date is blank but the received date has been filled in. Apologies if I was not clear on that in my OP. I may be asking too much of the calculated column.
    – Chris
    Oct 15, 2020 at 11:11
  • See the updated formula. Let me know if it still doesn't work for you
    – Matiur Rahman
    Oct 15, 2020 at 12:32
  • Mr. Rahman, you are the man! This worked perfectly. Thank you for your response.
    – Chris
    Oct 15, 2020 at 15:30
  • Mr. Rahman, one more question for this syntax. How would I exclude Weekends from the calculation? =IF(AND(NOT(ISBLANK([FOIA Date Received])),ISBLANK([FOIA Date Completed])),DATEDIF([FOIA Date Received],TODAY(),"d"),"")
    – Chris
    Oct 16, 2020 at 15:30
  • Little complicated, but has already been answered at here
    – Matiur Rahman
    Oct 16, 2020 at 15:39

Your Answer

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

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