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

[abuchanan]

I'm wanting to create a way to track tasks by entering a START DATE, and then in the next column allow you to pick if you want to enter an END DATE or the number of days you want to allow for the task to be completed. For example, the TASK could be Create a Presentation in A1. Then I would put in B1 the START DATE, June 1. Then in C1 I can decide if I want to set an END DATE, or if I want to set the number of DAYS ALLOWED. I figure I could do this by putting a drop down in C1 where you choose 1 to set an END DATE and 2 to set # OF DAYS.

If I choose 1 (END DATE), I want to be able to type in the END DATE in D1 - making sure it is a weekday, and auto fill the number of days allowed in E1.

So I type in START DAY June 1 in B1, and validate that it is a weekday. Then I choose 1 in C1 to enter the end date, and enter JUNE 8 in D1. Then E1, DAYS ALLOWED should auto populate with a 5, since there are 5 workdays between June 1 and June 8.

Someone else may want to choose to enter the DAYS ALLOWED field, so they would choose 2 in C1, and enter the 5 in E1, and then D1 should auto populate and end JUNE 8.

Having multiple problems with this, but here's what I have so far.

For START DATE B1, I validate that it is a WEEKDAY.

For C1, I just entered a comment to choose 1 or 2 depending on how you want to work (either putting in END DATE or # OF DAYS. (I tried to do a drop down box with something like "I want to enter the END DATE", or "I want to enter the # OF DAYS ALLOWED" however, I didn't know how to allow these options but actually show a 1 or 2 in the cell...)

For D1, I can't figure out the IF statement to use here. It should check for a 1 in C1, and if TRUE, then just allow you to enter a date and have it validated that it is a weekday. Don't know how???

Then for E1, it should check for a 2 in C1, and if TRUE, it should allow you to put in a number, then add that number of weekdays to the start date to fill D1.

Hopefully this makes sense. I may be approach this completely wrong. I just don't understand when to use FORMAT, VALIDATE or an IF STATEMENT to try and make this work.

Any of you Excel Wizards out there able to help me?

Thanks much.

 

[etaf]

does this work for you

date or days - ETAF.xlsx
	A	B	C	D	E	F	G
1		startdate	Enddate or days	days/End date			End Date
2		1/1/23	Number of Days	23	2/1/23		Number of Days
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	Expression	=C2="End Date"	text	NO
D2	Expression	=C2="number of days"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$G$1:$G$2
D2	Custom	=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

I have used
END DATE
Number of days
in column G for a dropdown list - this can go anywhere
data validation list

Then used conditional formatting to format the D2 - as a date - if "end date" chosen
and if Number of days - then format general

in B2 enter the start date
in C2 - choose End Date or Number of days

data validations
=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

which checks if date chosen then check its a weekday

in E2
=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))
if end date than calculates the number of work days
if days - then works out the end date based on workdays

i have also added to dropbox - only be on for a few days

is this anything like you wanted

I know it does use the 1 or 2 - but from the text i assume the words would be better

Dropbox - File Deleted - Simplify your life

www.dropbox.com

But happy to change around - just thought a starting point for you to try

 

[etaf]

breaking the above post down a bit to help explain what i have done

If I choose 1 (END DATE), I want to be able to type in the END DATE in D1 - making sure it is a weekday, and auto fill the number of days allowed in E1.

A list in G for a data validation list - which has End Date and Number of days - used data validation in C2
now when you choose
END DATE in B2
you can enter a date in to D2
Data Validation
has
=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

So I type in START DAY June 1 in B1, and validate that it is a weekday. Then I choose 1 in C1 to enter the end date, and enter JUNE 8 in D1. Then E1, DAYS ALLOWED should auto populate with a 5, since there are 5 workdays between June 1 and June 8.

If end date is chosen then it tests to see if its a weekday - if C2 has end date selected using
=AND(C2="end date",WEEKDAY(D2,2)<6)

then in E1
=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))
so if C2 has end date - it will use network days to calculate the number of days

D2 is conditional formatted to change the format to a date format
using a formula
=C2="End Date"
then format to date

Someone else may want to choose to enter the DAYS ALLOWED field, so they would choose 2 in C1, and enter the 5 in E1, and then D1 should auto populate and end JUNE 8.

In this case in C2 they choose number of days

in data validation we had
=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))
so no check on if a date

=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))
now c2 does not = end date and so the formula is
WORKDAY(B2,D2)

and calculated the end date based on working days

 

[abuchanan]

Thanks for your help Wayne!
I'm looking at it - and if I could get off the phone I could check it out better! lol lol

So if I choose End Date in C2.... it doesn't look like E2 is showing me the number of days????
I choose End Date in the drop down... then I entered 1/5/23 in D2... but it is giving me a date in E2 instead of number of days...

Or am I doing something incorrect?

(also, I am assuming you are using the European date... i.e. enter Day, Month, Year as opposed to the U.S. way... for Month/Day/Year?

 

[etaf]

what are you entering in D2 ?(also, I am assuming you are using the European date... i.e. enter Day, Month, Year as opposed to the U.S. way... for Month/Day/Year?

(also, I am assuming you are using the European date... i.e. enter Day, Month, Year as opposed to the U.S. way... for Month/Day/Year?

Like Quote Reply

Yes , i'm in UK so DD/MM/YY =- often XL2BB changes to US format

this is using end date

date or days - ETAF.xlsx
	A	B	C	D	E	F	G
1		startdate	Enddate or days	days/End date			End Date
2		1/1/23	End Date	2/1/23	23		Number of Days
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	Expression	=C2="End Date"	text	NO
D2	Expression	=C2="number of days"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$G$1:$G$2
D2	Custom	=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

and using number of days

date or days - ETAF.xlsx
	A	B	C	D	E	F	G	H
1		startdate	Enddate or days	days/End date			End Date
2		1/1/23	Number of Days	23	2/1/23		Number of Days
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E2	Expression	=C2="End Date"	text	NO
D2:E2	Expression	=C2="number of days"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$G$1:$G$2
D2	Custom	=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

i have also set the conditional formatting to show - general or date in E2 as well - perhaps that was the issue

 

[abuchanan]

Hey Thanks again...

I think it is working, but is there anyway to have the correct format automatically change?

In other words, if I choose END DATE, I'm fine... but then if I flip over to # of Days, and enter say 10 days... then unless I change the format, it wants to put in a date. I'm thinking maybe some type of conditional formatting????? I'm playing with that, but you're a MUCH better excel guy than me!

I'm actually trying to work on a "kind of" Gantt Chart thing...

Also for some reason, (and this is likely user error... ME!!)....the old "loose nut behind the keyboard"

But the formatting on Column B I THINK is the same as formatting on D, but for some reason, it is thinking B is the US format and D is the European format? Or am I crazy????

 

[abuchanan]

Strange... when I change the format for ColB between English US and English GB, it changes like it should...

But when I do the same thing for ColD, it won't change to English US....I MUST be doing something crazy!

 

[etaf]

try this dropbox version

date or days - ETAF-1.xlsx

Shared with Dropbox

www.dropbox.com

date or days - ETAF-1.xlsx
	A	B	C	D	E	F	G	H
1		startdate	Enddate or days	days/End date			End Date
2		1/1/23	Number of Days	240	01/12/2023		Number of Days
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(C2="end date",NETWORKDAYS(B2,D2),WORKDAY(B2,D2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	Expression	=$C2="End date"	text	NO
E2	Expression	=$C2="Number of days"	text	NO
D2	Expression	=$C2="End Date"	text	NO
D2	Expression	=$C2="number of days"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$G$1:$G$2
D2	Custom	=OR(C2="number of days",AND(C2="end date",WEEKDAY(D2,2)<6))

 

[abuchanan]

That seems to work... except for the crazy US/GB formatting.

For some reason, on my mac, Column B is still in the English US... so if I enter Jan. 2, 2023 and I type in 01/02/23 as we do here in the states, the result shows 01/02/23 just as it should for us Yanks... lol

But is I pick # of days, and type in 4... it shows the results in ColD as 06/01/23 as you would do in GB... even though the format says is in English U.S.

And if I pick End Date, and I type in 01/06/23 -- for Jan. 6, 23... it again shows it as 06/01/23. (so to me it looks like I'm entering Jan 6 and it is showing June 1! lol

Don't understand what is going on... some way the formatting rules seem to have the country of origin "built in" ??? or maybe it is a system default, though I don't know why Col B would use the US format....

That may be something we can't fix? Or I may try to copy your formulas over to a new workbook that I open... in fact, if you don't know of a reason, I'll try that!

Again, Wayne, this has been a TREMENDOUS help. I used to know excel a LOT better when I was younger, but I haven't used it nearly as much in the last few years as I did early in my career!!!!!

 

[etaf]

i changed the formatting of dates to be MM/DD/YY
its in the cell B2 - and also in conditional formatting for D2 and E2
see if that helps

I expected the dates to be based on your system date , and so change

i'm also using a Mac

Dropbox - File Deleted - Simplify your life

www.dropbox.com