Tip #3 – Date formulas
When you want to enter a date into your spreadsheet, you have several options. You can, of course, enter the date directly into a cell. Or you can use the keyboard shortcut CTRL + ; which will enter today’s date into the active cell. But suppose you have a file where you want the date to always show the current date. You can enter the date in every time you open the file, but shouldn’t there be a way to avoid this step? Indeed there is! Enter this appropriately named formula into your spreadsheet:
With this formula, the date will automatically change whenever you open the file in a new day. No more manually changing the date!
When working with dates, I’ll often need to know what day of the week I’m looking at. In my line of work, I compare week over week numbers to analyze any trends. But I have to be sure to look at the same day of the week. I can’t accurately compare a Sunday with a Friday (apples to oranges, and all that). Let’s suppose you’ve entered a date in cell A1. You want to put the day of the week in cell B1. We can take the hard approach by looking up the date in a calendar or perhaps trying to remember what day of the week a certain date was. I don’t know about you, but whenever I try this last method, it very rarely works. Instead, enter the following formula in cell B1:
This formula returns a number from 1 to 7, corresponding to the correct day of the week (Sunday = 1, Monday = 2, Tuesday = 3, etc.). Now, you can enter a date in cell A1 and know immediately what day of the week it is.
“BUT,” you say, “this result is still a little too cumbersome. I don’t want to have it return a 1, 2 or whatever. I want to know the day!” No problem. Sometimes having the numeric value for a day of the week is handy, but it certainly isn’t very pretty. Still keeping your date in cell A1, enter the following formula in cell B1:
Notice that we’ve incorporated the same WEEKDAY formula from above into another formula. When you have multiple formulas in one cell, this is called nesting. Now we introduced a new formula, the TEXT formula. This formula takes the number given to us by the WEEKDAY formula and turns it into text. The “dddd” (it must have the quotation marks around it) tells the formula that you want to know the name of a weekday. So if your date in cell A1 was 12/21/2012, this nested formula would let you know that the Mayans have the world ending on a Friday. TGIF?
That’s all for this week! And remember, if you have any questions, feel free to leave a comment or send me an e-mail at eskypades[at]gmail.com
Filed under: Excel, Tuesday Tip | Leave a Comment »