True or False if condition match

[Vishaal]

Hi we have the following sheet where we want to track work is pending or not when date is over

Condition is
1. IF(A+7=TODAY DATE)
2. IF(A+7<TODAY DATE)
3. IF(F=BLANK)
4. Formula will not count Sunday or Holidays

if all above condition match then in column h, Pending otherwise Completed

In second want to add a conditional formatting if the above condition match then that row will be red

New Microsoft Excel Worksheet - Copy (version 1).xlsb
	A	B	C	D	E	F	G
1	Date	Order	P Name	Qty		Order Received on	Recd. Qty
2	27/4/2023	Given	TEST 1	100,250		29/4/2023	100,250
3	29/4/2023	Given	TEST 2	250,325		4/5/2023	250,300
4	4/7/2023	Given	TEST 3	650214
5	5/7/2023	Given	TEST 4	956321		10/7/2023	956320
6	5/7/2023	Given	TEST 5	658,745
7	6/7/2023	Given	TEST 6	500
8	11/7/2023	Given	TEST 7	54800
9	12/7/2023	Given	TEST 8	65,914
Sheet4

 

[AhoyNC]

Try:

Book4
	A	B	C	D	E	F	G	H
1	Date	Order	P Name	Qty		Order Received on	Recd. Qty
2	4/27/2023	Given	TEST 1	100250		4/29/2023	100250	Complete
3	4/29/2023	Given	TEST 2	250325		5/4/2023	250300	Complete
4	7/4/2023	Given	TEST 3	650214				Pending
5	7/5/2023	Given	TEST 4	956321		7/10/2023	956320	Complete
6	7/5/2023	Given	TEST 5	658745				Pending
7	7/6/2023	Given	TEST 6	500				Complete
8	7/11/2023	Given	TEST 7	54800				Complete
9	7/12/2023	Given	TEST 8	65914				Complete
Sheet6

Cell Formulas
Range	Formula
H2:H9	H2	=IF(AND(A2+7<=TODAY(),F2=""),"Pending","Complete")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:H9	Expression	=$H2="Pending"	text	NO

 

[Vishaal]

Thanks AhoyNC,

Sorry if I mentioned wrong, but if there is a Sunday then it will show wrong, how can we rectify that

Thanks

 

[AhoyNC]

What do you want if a Sunday? What should be returned?
Not sure what you mean by "4. Formula will not count Sunday or Holidays".

 

[Vishaal]

we are using A2+7
In A2 date is 27th April 2023 and if we add seven days it will be 4th May 2023 but there is a Sunday on 30th April 2023

so that we want if there is any Sunday then it will add one more day formula