What is the exact date for Earth Day in 2010?

Date criteria in

• QUESTION: I am making a School Course Catalog for my job. I need to make a table for the class dates. Some classes are the 2nd Tuesday of every month for a fiscal year. Is there a formula I can use inthat will populate the dates for me so I don't have to manually enter every date? ANSWER: Dawn, Let us say your fiscal year starts in July. In any cell put in this formula =DATE(2010,ROW(A1)+6,1+((2-(3>=WEEKDAY(DATE(2010,ROW(A1)+6,1))))*7)+(3-WEEKDAY(DATE(2010,ROW(A1)+6,1)))) format that cell as Date is if doesn't do that automatically. Now drag fill it down the column. The row(A1)+6 in 3 locations in the formula determine that the first month in the Fiscal Year is July. Row(A1) returns a 1 and adding 6 to it produces a 7 for the month argument of the date function. As you drag fill this down the column, the A1 will become A2, then A3, adding a one to the month argument in each row. If you want to go across the columns rather than down rows, then you need to use this formula =DATE(2010,column(A1)+6,1+((2-(3>=WEEKDAY(DATE(2010,column(A1)+6,1))))*7)+(3-WEEKDAY(DATE(2010,column(A1)+6,1)))) Even though the year is fixed as 2010, when you drag fill this formula, you will eventually be adding month 13, then month 14 and so forth.will automatically see that as extending into 2011. the 3 located in two places in the formula determines that you want a tuesday. and the 2 in in the formula for 2-(3>=WEEKDAY(DATE(2010,ROW(A1)+6,1))) determines the Nth occurance in a month. So a 1 there would be the first Tuesday and a 3 would be the third Tuesday. the general form of the formula is =DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+(DoW-WEEKDAY(DATE(Yr,Mon,1)))) where DoW represents the day of the week (Sunday: 1, Monday: 2, Tuesday: 3) Nth is the Nth occurance within the month - so for your that is 2. Yr is the year where you are starting. Mon is the Month number (1 - 12) and the 1 is used for the first of the month. I have used either the Row() or Column() functions for the month so it progresses as you drag fill the formula. the row() function returns the row number of the argument. So row(A1) returns a 1. Likewise, the column() function returns the column number of the argumen. So Column(A1) returns a 1; column(B1) returns a 2 and so forth. Hopefully I have provided a clear explanation of the function you requested and you will be able to put it right to work. If any confusion, please post a followup with your concerns. ---------- FOLLOW-UP ---------- QUESTION: I understand that perfectly thank you. I just have an additional question. What if I need the 1st and 3rd Friday of every month in a fiscal year? Can I adjust this formula to return those two dates as well? ANSWER: Dawn, Yes you can: =DATE(2010,TRUNC((ROW(A1)-1)/2)+7,1+((IF(MOD(ROW(A1),2)=1,1,3)-(6>=WEEKDAY(DATE(2010,TRUNC((ROW(A1)-1)/2)+7,1))))*7)+(6-WEEKDAY(DATE(2010,TRUNC((ROW(A1)-1)/2)+7,1)))) would be the formula you drag down the column. we have put an If statement in the place where the formula has the Nth occurance. The IF statement is IF(MOD(ROW(A1),2)=1,1,3) this returns a 1 if the row is Odd and a 3 if the row is even. so on alternating rows, it will return either the 1st or 3rd Friday. The Friday is chosen by changing the 3 we had previously to a 6 in two places. Now the other thing we need to do is have it return the same month in two consecutive rows. In the previous, the month incremented in each subsequent row. We can do this by dividing the row number by 2 and truncating any decimal portion so instead of having row(A1)+6 we have TRUNC((ROW(A1)-1)/2)+7. IN the first two rows, Trunc(row(A1)-1)/2) will return zero Trunc((row(A2)-1)/2) will also return zero in the next two rows after that, they will return 1. So adding a 7 to the result will start in July for the first two rows, then go to August for the second two rows. As previously, to drag fill to the right, replace the Row with column. ---------- FOLLOW-UP ---------- QUESTION: You are explaining this so clearly. I would have never figured this out...never even seen TRUNC command inwow. One more. What if I have a class every Wednesday for the entire FY?

• Answer:

  Dawn, put the start date (first Wednesday date) in say B2 in B3 put in the formula =B2+7 format the cell with the formula as a date now select this cell (B3) and drag fill down the column. Dates are stored as the number of days from a base date. In Windows, that base date is Midnight going into 1 Jan 1900. So adding 7 to the first date puts you at the next wednesday and this ripples down as you drag the formula.