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.

 

[abuchanan]

YES. That did it!!!!!!!!!

Strange... but then excel is strange sometimes!

Wayne... you have been so much help!!!!!!

I am creating a Gantt Chart to help keep track of steps for a class we are doing at the University of South Florida. No one in our department knows enough about excel... and you would think I could find a student out there that would know, but trying to find someone (and find someone that understands what I've even asking) is next to impossible!!!!

There are a lot of videos on the internet that show you how to create a Gantt chart, and I've watched a lot of them, but none that I can find allow you the option of how you want to get the end date like you did for me! And I'm working with folks that don't really know excel a lot... plus some of them always want to say "let's give this 10 days". and then others want to say "the close date should be June 13... or whatever".... and I KNOW that if I force them to do one or the other and don't give them the option, I'll hear complaints!!!!!!

Now I need to watch more videos to set up the time line, and precedents and all of that other Gantt chart junk!!!!

You have been great! And you UNDERSTOOD me!!! That's a big part of the battle!!!

Thanks again, and hope you're having a great time across the sea...

(While I do some stuff for University of South Florida, I actually live in Atlanta, GA... I used to live in Tampa - where USF is located, and a friend is the department director there... so that's my "connection"...

thanks again!!!!!

 

[etaf]

you are welcome

If you need help on creating a Gantt chart, please start a new thread here, as its a new question.

i have created a few gantt chants for business and for replies here and other forums , as i'm sure a lot of other members have, and can help

you can use my info as a starting points , as you now have the Start and End dates - then you may want a few other fields - such as task, open/closed etc

it would be worth making a rough idea of what you want and the expected results - see how to post below

here are some examples and videos i have used in the past - just to maybe get you started ....

as I say a new thread is needed as a new question

Gantt Plan.xlsx

Shared with Dropbox

www.dropbox.com

Gantt.xlsx

Shared with Dropbox

www.dropbox.com

Gnatt Chart_Conditional_Formatting.xlsx

Shared with Dropbox

www.dropbox.com

A SMALL sample spreadsheet, around 10-20 rows, would help, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[abuchanan]

Will do. I'm going to mark this solved!

Thanks again... I'll watch your videos and check the files out. Thanks again Wayne!