Excel/Interactive Holidays on excel
QUESTION: Hi Richard,
I'm an intern and took my excel class like three years ago as a freshman and I don't think I ever learned how to do dates. I went online searching for a formula that the beginning date would be 10/01/2012 with is 41183 in excel. I want the next cell under that one to show one week elapsed: 10/08/12 and under that 10/15/2012 and so on until 12/28/2015. Can you help? I looked at websites but either it wasn't what I wanted or it was too difficult to follow. Thanks. (all days are sundays)
You have a start, The formula is pretty easy
If we assume that the date 10/1/12 is in cell A1,then the formula in cell A2 should be---
As you can see we are just adding 7 days to your number of 41183 and making it 41190 which is 10/8/12.
This formula can be copied downward and will change appropriately because of relative referencing.
Of course you will have to format the cells as date.
Hope this works for you.
---------- FOLLOW-UP ----------
QUESTION: Thank you so much. It was so awesome how I just just drag the box down and it would give me so much data. On the same excel sheet in a different column I have to reference holidays and a number. For example: Thanksgiving is in the week 11/26/2012 and the office has 2 days off. In the cell, I have to write Thanksgiving (2). In the year 2013, the week is 11/25/2013, and I have to get the same cell to show up: Thanksgiving (2).
Hint: Thanksgiving is in the fourth week of November. I think unless the first day of the month was a Saturday. If so, I'm not absolutely sure.
I am so sorry but I'm not done yet. I believe that a second formula is needed for when the holidays are on a certain date. For example: New Year's (2) is always on January first. You couldn't write a formula that searches for a specific date because the date is not listed. Only the Sundays are used as time. Could you use what week the holiday would be on? That wouldn't work because the first Sunday of the year is used as the beginning and the master calendar starts on 10/01/12.
I'm providing my thought process because I want you to know that I work hard on trying to figure out it out myself but I do really need your help.
As easy as the answer to your first question was, the response to your second is much more difficult. that is mainly because excel doessn't have a function that automatically identifies holidays for us. So what that means is that you must identify the holiday dates for excel. Of course the fixed holidays remain the same except as to year, but the floating holidays would have to be looked up to identify each of them. You can create a list of holidays for the years you are working with and then create an IF formula in the column that would identify the holiday and put in the text that you want. For example the formula might look like this ---
This formula is next to the cell that contains the week beginning 12/24/12, which is A13.
This formula is saying if the cell A14 which is the week beginning 12/31/12 is greater than 41268 which is the numeric equivalent of 12/25/12, and if the value in cell A13 is less than 41268, then put the value from cell F1 in the formula cell. F1 contains Christmas-2. It goes on to say if the result of the formula is false then put a blank in the cell.
This would be the formula for Christmas 2012. In the same cell the formula would need to continue to test for thanksgiving, labor day, Easter, etc, etc. So the formula could get very long and involved. Theere are other ways to create the formula but none are any easier. here is a reference to holiday lookup problems where you can get excel to look up the holiday dates itself.
As you will see if you look at that site, it is a very thorny issue.
I believe it will be easier for you to manually review each date in your column and if it contains a holiday enter the information you need.
A simple way to do this would be to create a coded list of what your holidays are and then manually enter the code next to your week beginning date, and then reference the code in a formula to bring over the text that you want. For example your partial list might be
cell X1=1 cell Y1=Christmas-2
cell X2=2 cell Y2=Easter-2
cell X3=3 cell Y3=Thanksgiving-1
So as you review the beginning week dates in the column you can then put in a code if the week contains a holiday. then in the next column you can create a formula that will bring over the information in column Y. The formula might look like this.
This is a lookup formula that looks up the value in column B cell 1, which is the cell you placed your code in. It looks up the value in your holiday list which in this case is in cell X1 thru Y3 as above. if it finds a code it brings back the value from the adjacent cell in column Y which is the 2nd column of the array(thius the reference to 2 in the formula). the false in teh formula merely tells excel that you only want an exact match to the code you are looking for. now this formula is great but if there is no match it will produce an error message #N/A. since we don't want that in every cell that doesn't contain a holiday we need to change the formula slightly to look like this.
This formula says if an error is produced then put in a blank if no error then put in the formula result.
Having said all of that as you can see it is a bit more complicated that you might have expected, but you can try the methods I have suggested.