Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I do this in Excel...create dates for tasks based on a date (long)
I work for a company that puts on trade and consumer shows.
I would like to set up a spreadsheet to create a calendar for all the shows. There are certain tasks that need to be done for every show 1 year out, 6 months out, 3 months out, 4 weeks, 2 weeks, 10 days, etc etc I am thinking of having the first sheet in the workbook listing all the shows with their dates. Based on the date of the show, I would like Excel to list tasks that need to be done. 1 year out... task 1 6 months out.....task 2 4 months out...task 3 3 months out..task 4 1 month out...task 5 1 week out...task 6 etc etc I will create a list of the tasks, I just want Excel to calculate the dates each task needs to be completed based on the date of the show. I think I want one sheet for all tasks and their due dates because we are often working on multiple events at the same time. So far I have: Sheet 1 Event start date end date event #1 event #2 event #3 event #4 Sheet 2 task 1 event 1 event 2 event 3 event 4 task 2 event 1 event 2 event 3 event 4 task 3 event 1 event 2 event 3 event 4 task 4 event 1 event 2 event 3 event 4 task 5 event 1 event 2 event 3 event 4 etc etc As I have it now I would like Sheet 2 to enter the due date for each task based on the starting date of the event (found in Sheet 1) and how far in advance of the start date it needs to be done. For task 1 event 1, I was thinking of entering (=Sheet 1, B2-6 months) For task 1 event 2, I was thinking of entering (=Sheet 1, B3-6 months) (with the proper formatting of course, which I do not know right now) etc etc If I set this up with the tasks that need to be done for every show, then as soon as I enter an event and the dates, I should have a complete work plan for the show! If you read all of this, thank you!! Thanks for any and all input. ....Lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I do this in Excel...create dates for tasks based on a dat
Hi Lisa,
Ill give you some formulas and perhaps you will be able to work it out from there. All of the formulas assume that the event date is in cell A2 and each formula is spread across the page from there. Event date in cell A2 is 27 Oct 2008. Year 1: =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)) 6 Months: =DATE(YEAR(A2),MONTH(A2)-6,DAY(A2)) 3 Months: =DATE(YEAR(A2),MONTH(A2)-3,DAY(A2)) 4 Weeks: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-28) 2 Weeks: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-14) 10 Days: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-10) Year 1 returns 27 Oct 2007. 6 Months returns 27 Apr 2008. 3 Months returns 27 Jul 2008. 4 Weeks returns 29 Sep 2008. (Note formula subtracts 28 days) 2 Weeks returns 13 Oct 2008. (Note formula subtracts 14 days) 10 days returns 17 Oct 2008. If you look up Date function in help then I am sure you will work out what the formulas are doing Feel free to get back to me if you need more information. Regards, OssieMac "hamiltoncruiser" wrote: I work for a company that puts on trade and consumer shows. I would like to set up a spreadsheet to create a calendar for all the shows. There are certain tasks that need to be done for every show 1 year out, 6 months out, 3 months out, 4 weeks, 2 weeks, 10 days, etc etc I am thinking of having the first sheet in the workbook listing all the shows with their dates. Based on the date of the show, I would like Excel to list tasks that need to be done. 1 year out... task 1 6 months out.....task 2 4 months out...task 3 3 months out..task 4 1 month out...task 5 1 week out...task 6 etc etc I will create a list of the tasks, I just want Excel to calculate the dates each task needs to be completed based on the date of the show. I think I want one sheet for all tasks and their due dates because we are often working on multiple events at the same time. So far I have: Sheet 1 Event start date end date event #1 event #2 event #3 event #4 Sheet 2 task 1 event 1 event 2 event 3 event 4 task 2 event 1 event 2 event 3 event 4 task 3 event 1 event 2 event 3 event 4 task 4 event 1 event 2 event 3 event 4 task 5 event 1 event 2 event 3 event 4 etc etc As I have it now I would like Sheet 2 to enter the due date for each task based on the starting date of the event (found in Sheet 1) and how far in advance of the start date it needs to be done. For task 1 event 1, I was thinking of entering (=Sheet 1, B2-6 months) For task 1 event 2, I was thinking of entering (=Sheet 1, B3-6 months) (with the proper formatting of course, which I do not know right now) etc etc If I set this up with the tasks that need to be done for every show, then as soon as I enter an event and the dates, I should have a complete work plan for the show! If you read all of this, thank you!! Thanks for any and all input. ....Lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I do this in Excel...create dates for tasks based on a dat
Perfect OssieMac!!
Thank you!! Is there a way tro modify the formulas so the dates fall only on Mon- Fri.... ....Lisa On Sep 30, 8:23 pm, OssieMac wrote: Hi Lisa, I'll give you some formulas and perhaps you will be able to work it out from there. All of the formulas assume that the event date is in cell A2 and each formula is spread across the page from there. Event date in cell A2 is 27 Oct 2008. Year 1: =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)) 6 Months: =DATE(YEAR(A2),MONTH(A2)-6,DAY(A2)) 3 Months: =DATE(YEAR(A2),MONTH(A2)-3,DAY(A2)) 4 Weeks: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-28) 2 Weeks: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-14) 10 Days: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-10) Year 1 returns 27 Oct 2007. 6 Months returns 27 Apr 2008. 3 Months returns 27 Jul 2008. 4 Weeks returns 29 Sep 2008. (Note formula subtracts 28 days) 2 Weeks returns 13 Oct 2008. (Note formula subtracts 14 days) 10 days returns 17 Oct 2008. If you look up Date function in help then I am sure you will work out what the formulas are doing Feel free to get back to me if you need more information. Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create and Assign Tasks | Excel Discussion (Misc queries) | |||
Scheduling staff to tasks based on seniority | Excel Discussion (Misc queries) | |||
How do i create a pie chart that shows % of time on tasks? | Charts and Charting in Excel | |||
How do I create a commission chart based on dates worked? | Excel Worksheet Functions | |||
CREATE A SUM BASED ON DATES | Excel Worksheet Functions |