Did you know that Excel can tell you the day of the week of any date?
The solution to that old question “What day of the week is New Years Eve next year?”
Behind every date in an Excel cell is a serial number.
As an example type into a cell in your spreadsheet the number 41268, re-format the cell to a date format and the cell now reads 25/12/2012. (Or similar depending on which date format is used). Excel uses these serial numbers for all date calculations. The value 1 is one day, or 24 hours.
Excel has a useful function named WEEKDAY(). This function returns a number that corresponds to the day in the week of a specific date. The default setting is to count from Sunday as day 1, so day 3 equals Tuesday, day 6 is Friday etc.
In later Excel versions there is a week start day option, using “Return_type”. See the help file for details. Leave this field blank, or enter 1 to keep the default as day 1 = Sunday.
Step by step: Choose any cell on your worksheet, let’s say it is A3, open the WORKDAY() function from the “Date & Time” icon in your formulas tab, see above. In the “Serial_number” field type in 41268. In the “Return_type” field leave blank or enter 1. Click the “OK” button. The result in A3 reads “3”, being the third day of the week.
As in all Excel functions the components can be the values in cell addresses. So instead of typing 41268 directly into the function it can be in a worksheet cell, let’s say it is A1.
In previous Monthly tips we discussed default formatting; When you type into a cell Excel sees “ABCDE” as text and left justifies it. Type in “41268” and Excel sees it as a number with right justification. Type in “25/12/2012” and Excel sees this and formats it as a date. Check for yourself. Type “25/12/2012” (without the parenthesis!) into cell A1. Highlight the cell, get the right click menu, and choose “Format Cells….”. Note the format is already “Date”. Change the format to “General” and our cell now reads “41268”.
Delete the contents of cell A1 and ensure the formatting is “General”. In the same cell type “25/12/2012”. Go back to the WEEKDAY() function in A3 and replace the value in the “Serial_number” field with the cell address “=A1”. Click the “OK” button. The result in A3 again reads “3”, being the third day of the week. By changing the date in A1 we can get the correct day of the week for any date. Q. What day is New Years Eve in 2013?
The clever chaps amongst you will use a table similar to the one above, and use the Vlookup() function to get the name of the day. What’s Vlookup()? Tut tut think you need some Extra-Ordinary XL training! BTW the answer is Tuesday.
Extra-Ordinary XL training can cost less than you think. Group or One-to-One coaching will pay early dividends, and ensure you get the most from your software investment.
For those special projects that your staff just cannot figure out, the spreadsheet that always throws up errors or the file that takes ages to update each month, our spreadsheet modelling service can help streamline your systems, reduce frustration and improve efficiency.
Call us now for a no obligation chat.