Trying to create a formula to check for due assignments

[htay44]

Hi!

I wondered what formula I could use to solve whether I have something due. I was looking for it to check if the date has passed in column B, and if column E says either "in progress" or "not started". If both of these conditions were met, column G would say "due", otherwise it would just be blank.

Any help would be greatly appreciated! Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this, in G3 and copied down works for you

=IF(AND(B3<TODAY(),OR(E3={"in progress","not started"})),"due","")

 

[jtakw]

Hi,

Like this?

Book3.xlsx
	B	C	D	E	F	G
3	1/10/2022			Completed
4	1/10/2022			Completed
5	1/10/2022			In Progress		Due
6	1/10/2022			Completed
7	1/11/2022			Completed
8	1/11/2022			Completed
9	1/12/2022			Not Started		Due
Sheet952

Cell Formulas
Range	Formula
G3:G9	G3	=IF(AND(B3<TODAY(),E3<>"Completed"),"Due","")