how can I do this in Excel...create dates for tasks based on a date (long)

[hamiltoncruiser]

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

 

[OssieMac]

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

 

[hamiltoncruiser]

Perfect OssieMac!!
Thank you!!

Is there a way tro modify the formulas so the dates fall only on Mon-
Fri....

....Lisa