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"))
Add a comment
|
1 Answer
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,"")
The updated formula
=IF(AND(NOT(ISBLANK(ReceivedDate)),ISBLANK(CompletionDate)),DATEDIF(ReceivedDate,TODAY(),"d"),"")
-
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.– ChrisOct 15, 2020 at 11:11
-
See the updated formula. Let me know if it still doesn't work for you Oct 15, 2020 at 12:32
-
Mr. Rahman, you are the man! This worked perfectly. Thank you for your response.– ChrisOct 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"),"")– ChrisOct 16, 2020 at 15:30
-