You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Interactive Holidays on excel

Advertisement

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)

ANSWER: Katie

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---

=A1+7

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.

Richard

Florida

USA

---------- 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.

Katie

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 ---

=IF(AND(A14>41268,A13<41268),+$F$1,"")

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.

http://www.cpearson.com/excel/holidays.htm

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.

=VLOOKUP(B1,X1:Y3,2,FALSE)

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.

=IF(ISERROR(+VLOOKUP(B1,X1:Y3,2,FALSE)),"",+VLOOKUP(B1,X1:Y3,2,FALSE))

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.

Richard

Florida

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

Have been working with Excel for about 20
years primarily in accounting and financial areas.**Education/Credentials**

BA, Certified Public Accountant