Can't figure out the formula.....

[madddogcom]

Working on a project tracking worksheet. My cells with formulas are as follows

• Duration is the standard number input for a typical task
• Start date is the Due Date or finish date minus the duration number: =J5-G5
• The Due/finish Date is just the date it was finished.
• The dates start on Monday for a whole week then the following week.

When the duration and due date is added, a formula in the week date column places an "X" there. With conditional formatting I then color the week green. Formula for adding the "X":=IF(AND(K$4>=$I5,K$4<=$J5),"X","")

The problem I have is anything less than 3 for a duration does not add the "X" in the work week and if a task finished on Tuesday and the task was 4 days work, thus starting on Thursday. My representation is off because we don't work weekends.

Can someone show me how to get the formula to work for anything less than 3 days and factor in no weekends also? I tried this, didn't work. =IF(or(weekday(K$4)=1,1)IF(AND(K$4>=$I5,K$4<=$J5),"X",""))

Regards,
Mike

 

[awoohaw]

Please try to post more than 1 example for the forum to try to figure out how to do this. Mr. Excel has a tool xl2bb add in (link below) that allows you to post miniworksheets. At minimum please post a table that has the base data as well as expected results.

But, this is my attempt at it:

Mr excel questions 58.xlsm
							G	H	I	J	K	L	M
4							Task Duration		Start Date	Due Date	Mon 2023/08/14	Mon 2023/08/21	Mon 2023/08/28
5							3		Mon 2023/08/21	Thu 2023/08/24	x	x
6							1		Wed 2023/08/23	Thu 2023/08/24		x
7							2		Tue 2023/08/22	Thu 2023/08/24		x
8							4		Fri 2023/08/18	Thu 2023/08/24	x	x
9							5		Thu 2023/08/17	Thu 2023/08/24	x	x
10							6		Wed 2023/08/16	Thu 2023/08/24	x	x
11							7		Tue 2023/08/15	Thu 2023/08/24	x	x
12							8		Mon 2023/08/14	Thu 2023/08/24	x	x
13							9		Fri 2023/08/11	Thu 2023/08/24		x
madddogcom

Cell Formulas
Range	Formula
L4:M4	L4	=K4+7
K5:M13	K5	=IF(OR( AND($I5>=K$4,$I5<=K$4+7), AND($J5>=K$4,$J5<=K$4+7) ),"x","")
I5:I13	I5	=WORKDAY.INTL(J5,-G5,1)

 

[madddogcom]

Thanks!!

That worked perfectly!!
Mike