Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation using dates... w/o actual dates
I am learning a lot looking through all these posts, but can't figure out
this answer. I am creating a spreadsheet of projects with a column for deadlines. I want to be able to select the deadline from a drop-down list/data validation. BUT I want the deadline choices to say things like, "today," "tomorrow," "by end of week," "in one week," "in two weeks," "in one month," etc. And then it would automatically input that as an actual "ddd m/d/yy" date. Also hope to list "Mon." "Tues." etc., where it would input the actual date of the next upcoming one. And if possible, one for the 1st workday of the following month, and one for the 15th of the following month (or the Mon after, if on a weekend). The user should also be able to input a particular date if they would like. I assume this would be done just by typing it directly in the cell, right? This deadline should NOT change as today's date changes. It must remain as it was originally input, unless it is manually changed. Oh - and it would be fine if one column had the drop-down box, and the next column had the actual date. This would help a ton, so I eagerly await any help you can offer! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation using dates... w/o actual dates
Wow, this sounds great, I will try it out! I really appreciate your help.
"paul" wrote: i though this sounded quite interesting.I have started a sheet,where there is a table with your deadlines in a vertical column and the next column is a column of formulas which calculate your deadlines The output cell is =a1+vlookup() today 0 tomorrow 1 day after tomorrow 2 next week 7 two weeks 14 end of week =IF(WEEKDAY($F$4,2)=5,7,5-WEEKDAY($F$4,2)) monday =IF(WEEKDAY($F$4,2)=1,7,8-WEEKDAY($F$4,2)) etc etc -- paul remove nospam for email addy! "thinartweakens" wrote: I am learning a lot looking through all these posts, but can't figure out this answer. I am creating a spreadsheet of projects with a column for deadlines. I want to be able to select the deadline from a drop-down list/data validation. BUT I want the deadline choices to say things like, "today," "tomorrow," "by end of week," "in one week," "in two weeks," "in one month," etc. And then it would automatically input that as an actual "ddd m/d/yy" date. Also hope to list "Mon." "Tues." etc., where it would input the actual date of the next upcoming one. And if possible, one for the 1st workday of the following month, and one for the 15th of the following month (or the Mon after, if on a weekend). The user should also be able to input a particular date if they would like. I assume this would be done just by typing it directly in the cell, right? This deadline should NOT change as today's date changes. It must remain as it was originally input, unless it is manually changed. Oh - and it would be fine if one column had the drop-down box, and the next column had the actual date. This would help a ton, so I eagerly await any help you can offer! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation using dates... w/o actual dates
if you see this message you can email me,see my email address and modify as
shown.Make sure you make the subject line very clear about subject and include your user name -- paul remove nospam for email addy! "thinartweakens" wrote: Wow, this sounds great, I will try it out! I really appreciate your help. "paul" wrote: i though this sounded quite interesting.I have started a sheet,where there is a table with your deadlines in a vertical column and the next column is a column of formulas which calculate your deadlines The output cell is =a1+vlookup() today 0 tomorrow 1 day after tomorrow 2 next week 7 two weeks 14 end of week =IF(WEEKDAY($F$4,2)=5,7,5-WEEKDAY($F$4,2)) monday =IF(WEEKDAY($F$4,2)=1,7,8-WEEKDAY($F$4,2)) etc etc -- paul remove nospam for email addy! "thinartweakens" wrote: I am learning a lot looking through all these posts, but can't figure out this answer. I am creating a spreadsheet of projects with a column for deadlines. I want to be able to select the deadline from a drop-down list/data validation. BUT I want the deadline choices to say things like, "today," "tomorrow," "by end of week," "in one week," "in two weeks," "in one month," etc. And then it would automatically input that as an actual "ddd m/d/yy" date. Also hope to list "Mon." "Tues." etc., where it would input the actual date of the next upcoming one. And if possible, one for the 1st workday of the following month, and one for the 15th of the following month (or the Mon after, if on a weekend). The user should also be able to input a particular date if they would like. I assume this would be done just by typing it directly in the cell, right? This deadline should NOT change as today's date changes. It must remain as it was originally input, unless it is manually changed. Oh - and it would be fine if one column had the drop-down box, and the next column had the actual date. This would help a ton, so I eagerly await any help you can offer! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation using dates... w/o actual dates
Hello again Paul,
Can you explain your idea a little further? I'm not exactly sure where to begin. When you say "table" do you mean a pivot table? I'm also not sure where you would put the output cell, and where you would use the 0, 1, 2, and other formulas. I'm still learning Excel's more advanced techniques, and am new to the discussion group, so that might explain why I am lost! Feel free to email if you like. Thanks so much! KT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
template for comparing projected vs actual milestone completion? | Charts and Charting in Excel | |||
Copy workbook- Validation function | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |