Forum Discussion
Formula Troubleshooting
I have an excel sheet that contains entries with both "Due" dates and "Completed" dates. On a separate sheet, I've created a formula that tallies the number of due dates in a specific month and year.
I'm now trying to create another formula that tallies the number of due dates in a specific month and year that are EITHER:
A. Earlier than the completed date (the entry was overdue when completed)
OR
B. Earlier than today AND are adjacent to a blank completed date (due date has passed and is still not complete)
I've created a formula that I believe should do just that, but, instead, it's tallying the total number of due dates, regardless of whether or not they were overdue. Formula is below:
=SUM(IF(ISNUMBER('Simplified Open and Complet'!J:J),IF(YEAR('Simplified Open and Complet'!J:J)=$A$122,IF(MONTH('Simplified Open and Complet'!J:J)=B122,IF(OR('Simplified Open and Complet'!J:J<'Simplified Open and Complet'!K:K,AND('Simplified Open and Complet'!J:J<TODAY(),'Simplified Open and Complet'!K:K="")),1)))))
For context:
- The J column of the "Simplified Open and Complet" sheet contains due dates.
- The K column contains the completed dates.
- A122 is the year of 2023.
- B122 is the month of January.
The formula should follow the following steps, in order:
- Check to make sure the due date is not empty
- Check to make sure the due date is in 2023
- Check to make sure the due date is in January
- Check to make sure the due date is EARLIER than the completed date OR (the due date is before today AND the completed date is blank).
If all of the above are true for a given row, the tally should increase by 1.
I'm sure I'm thinking of something incorrectly in my above formula, but can't figure out where I went wrong. Any help would be appreciated; thank you!
=SUM((NOT(ISBLANK('Simplified Open and Complet'!J2:J27)))*(YEAR('Simplified Open and Complet'!J2:J27)=A122)*(MONTH('Simplified Open and Complet'!J2:J27)=B122)*(('Simplified Open and Complet'!J2:J27<'Simplified Open and Complet'!K2:K27)+(('Simplified Open and Complet'!J2:J27<TODAY())*(ISBLANK('Simplified Open and Complet'!K2:K27)))))An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
6 Replies
- mtarlerSilver ContributorI would recommend if you have COUNTIFS to use that:
=COUNTIFS('Simplified Open and Complet'!J:J, "<>",
'Simplified Open and Complet'!J:J, "<"&TODAY(),
'Simplified Open and Complet'!J:J, "<" & 'Simplified Open and Complet'!K:K,
'Simplified Open and Complet'!J:J, ">=" & DATE(A122, B122,1),
'Simplified Open and Complet'!J:J, "<" & EDATE(DATE(A122, B122,1),1) )- FHuffmanCopper Contributor
Hi mtarler; thanks for the quick response!
When using the above formula, I'm getting a count of 0. The resulting number should be 26, some of which have a blank completed date (K column) and others that have a completed date later than the due date (J column). Only 9 have completed dates that are earlier than the due dates.- OliverScheurichGold Contributor
=SUM((NOT(ISBLANK('Simplified Open and Complet'!J2:J27)))*(YEAR('Simplified Open and Complet'!J2:J27)=A122)*(MONTH('Simplified Open and Complet'!J2:J27)=B122)*(('Simplified Open and Complet'!J2:J27<'Simplified Open and Complet'!K2:K27)+(('Simplified Open and Complet'!J2:J27<TODAY())*(ISBLANK('Simplified Open and Complet'!K2:K27)))))An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.