Using IF STATEMENTS, CONDITIONAL FORMATS and VALIDATE to track tasks. HOW? PT. 2

[abuchanan]

I posted yesterday, (don't know how to reference post), and Wayne was nice enough to help me out. However, last night I realized I failed to specify exactly what info I wanted to retain in the worksheet. This time I've taken his suggestion and pasted an example workbook.

What I'm wanting to do it to track tasks in one worksheet by the number of days a task takes versus the original estimate.

The issue is that I want to allow the user the option of entered EITHER an estimated END DATE for a task, OR an estimated NUMBER of DAYS the task will take. Wayne came up with a solution, but I had him using COLUMN A for the TASK, a drop down in COLUMN B to choose which you wanted to enter, and then in the next COLUMN entering the info.

So in Column C, you may have END DATE or NUMBER of DAYS.

But what I really need is to enter the END DATE in COLUMN C OR the NUM of DAYS in COLUMN D... and then the other COLUMN would calculate the appropriate DATE or Num of Days...

That way, for each task, I'm showing a START DATE in B; an END DATE in C, and a NUMBER OF DAYS in D....

It is just that you enter either DATE or DAYS and the other one automatically populates.

Several problems:
1). How do I enter a formula in a cell that will calculate OR just let you manually enter data?
2). I was also trying to use conditional formatting to turn the cells you manually enter data into GREEN, and those you let CALCULATE turn red (I almost got this one with Wayne's help);
3). Once I do manually enter data into the cell, the formula is gone... what if I then decide that instead of days I want to enter date?
EXAMPLE... A start date is June 1. I choose to enter 10 days. When the END DATE is calculated, I notice it is the first day of my vacation... so I decide I just will enter the END DATE... but since I previously entered 10 days, the formula to calculate the number of days is gone... I can enter the day I come back from vacation, but it no longer will calculate the number of days, since I erased the formula when I originally put in 10 days! (I was trying to do some sort of reset button that would put the formulas back in... but didn't know how the heck to start that! --- was thinking maybe once the "other" data is calculated -- either the END DATE or NUM of DAYS -- the cells all turn red... and if you want to change something you have to click a reset button and start over????? - Goofy way to do this I guess?

Anyway, hopefully this makes since to one of you guys. I'm attaching the example workbook.

I tried to use xl2bb to create a worksheet... hopefully this works????

Book3
	A	B	C	D	E	F	G	H	I	J	K
1			START DATE	Enter Data In Green Box ONLY	Est END DATE	Est NUM OF DAYS	ACTUAL END DATE	Number of Days OVER/UNDER
2		Determine Budget	6/1/23	NUM of DAYS	6/8/23	5	6/5/23	-3
3		Pick Vendor	6/15/23	END DATE	6/20/23	4	6/21/23	1
4		Place Order	6/15/23
5
6					HOW DO I DO CONDITIONAL FORMATTING SO A THE CELL TURNS
7					RED OR GREEN BASED ON COLUMN D?
8
9
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF($D2="Num of Days", WORKDAY($C2,$F2))
F3	F3	=IF($D3="end date",NETWORKDAYS($C3,$E3))
H2:H3	H2	=(NETWORKDAYS($C2,$G2)-(NETWORKDAYS($C2,$E2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3	Other Type	Color scale		NO
F2	Expression	=$D2="Num of Days"	text	NO
E2	Expression	=$D2="Num of Days"	text	NO

 

[Peter_SSs]

don't know how to reference post

• Go to the post (or thread) that you want to reference and copy the address from the address bar of your browser.
• Then in your current post reply window click the Link icon above the window
  
  
• Paste the copied address into the URL box and click 'Insert'
  
  

It should turn out like this when you post
Using IF STATEMENTS, FORMATS and VALIDATE to track tasks. HOW?

 

[abuchanan]

Thanks for the info. Hopefully someone will help me out with this today!

 

[fjns]

Hi, see the linked file (without your D column and without reset button) for a possible solution...

It is important to note that the WORKDAY and NETWORKDAYS functions are not inverse functions of each other.
For example WORKDAY(6/1/23,5)=6/8/23 but NETWORKDAYS(6/1/23,6/8/23)=5 is not TRUE but NETWORKDAYS(6/1/23,6/8/23)=6 TRUE.
Another example: NETWORKDAYS(6/15/23,6/20/23)=4, but WORKDAY(6/15/23,4)=6/20/23 is not TRUE, but WORKDAY(6/15/23,4)=6/21/23 TRUE.

The formulas used in the table...
F2: =IF(AND(D2="",E2<>""),WORKDAY(C2,G2),IF(AND(D2<>"",E2=""),D2,""))
G2: =IF(AND(D2<>"",E2=""),NETWORKDAYS(C2,F2),IF(AND(D2="",E2<>""),E2,""))
I2: =IF(G2="","",NETWORKDAYS(C2,H2)-NETWORKDAYS(C2,F2))

The formulas for the Conditional Formatting...
=AND(D1="",E1="",C1<>"")
=AND(D1<>"",E1<>"",ROW()>1)
=AND(D1="",E1<>"")
=AND(D1<>"",E1="")

Valid.xlsx

 

[abuchanan]

This is great!!! I changed a few things, but without your help would never have finished it!

Cool that you can put in date or number of days, and it figures the other for you. You never know how other people like to work, so this allows them the option!

Thanks very much again!! I really appreciate you and this board! I've been a long time member, but retired a few years ago and haven't done a lot in excel, so I've forgotten a LOT! (plus getting old doesn't help!)

Thanks again... I'm mark solved!

 

[fjns]

Hi, I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.