Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Mark a Date as "Overdue 10-13 days" in Google Sheets

I currently have a sheet that looks at column "I" and displays in column "J" if the date is:
"Due in 8 days"
"Due in 7 days"
"Due in 6 days"
"Due in 5 days"
"Due in 4 days"
"Due in 3 days"
"Due in 2 days"
"Due Tomorrow"
"Due Yesterday"
"Due Today"
"Overdue within 7 days"
"Overdue within 14 Days"
"Overdue within 21 days"
"Overdue more than 30 days"

Formula:

=IF(I14=TODAY()+8, "Due in 8 days", 
IF(I14=TODAY()+7, "Due in 7 days", 
IF(I14=TODAY()+6, "Due in 6 days", 
IF(I14=TODAY()+5, "Due in 5 days", 
IF(I14=TODAY()+4, "Due in 4 days", 
IF(I14=TODAY()+3, "Due in 3 days", 
IF(I14=TODAY()+2, "Due in 2 days", 
IF(I14=TODAY()+1, "Due Tomorrow", 
IF(I14=TODAY()-1, "Due Yesterday", 
IF(I14=TODAY(), "Due Today", 
IF(I14>TODAY()-7, "Overdue within 7 days", 
IF(I14>TODAY()-14, "Overdue 14-17 Days", 
IF(I14>TODAY()-21, "Overdue 20-23 days", "Overdue 30-33 days")))))))))))))

THE QUESTION: What formula can I use to find:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

"Due 4-7 days" and "Overdue 10-13 days"

>Solution :

Following both comments by @Matt

You’d need to change the formula.

and @Terry

Correct. But I can’t find an example formula for something like "Overdue 10-13 days"

You should follow this pattern

=IF(AND(I14<TODAY()-14,I14>TODAY()-17), "Overdue 14-17 Days","rest_if_formula")

Do try figuring it out yourself (you can do it)

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading