Have you ever wondered how many shopping days are left until Christmas or what day of the week is Boxing day? How many days until payday? Or which day of the week is my birthday? Maybe you're forever asking yourself am I Wednesday's child or Friday's child?
Hurl your diary away, spurn your smartphone, ignore your fingers and turn to Excel for the answers to all these date conundrums. All Excel dates are stored as numbers and a few simple formulas and functions combined with a passing knowledge of date format codes is all you will ever need. Just make sure that you type-in your dates correctly otherwise you won't be able to use them in your calculations and your date formats won't work.
The main thing is to make sure that the entry you make evaluates properly to a date and the best visual check is to see whether your date aligns to the right hand side of the cell but its always worth checking in the formula bar to see whether your date looks like this, 09/12/2014. When a cell contains a date you can make the date appear however you like by applying a date format.
Entering Dates
Whenever you type a date into an Excel cell remember to use slash or dash separators like this: 9/12/14 or like this 9-12-14. It's years since we had to worry about the Millennium bug but if you're feeling fussy or you need a date in the last century then use 4 digits for the year i.e. 9/12/2014. Simply type-in the day and month and Excel presumes that it's the current calendar year so entries like this 9/12 or 9-12 will suffice. Please don't use dot separators like this, 9.12.2014. They won't work unless you first change your Windows Control Panel settings.The main thing is to make sure that the entry you make evaluates properly to a date and the best visual check is to see whether your date aligns to the right hand side of the cell but its always worth checking in the formula bar to see whether your date looks like this, 09/12/2014. When a cell contains a date you can make the date appear however you like by applying a date format.
How many shopping days to Christmas?
I am going to presume that you will be able to shop on a Sunday but you will not want to venture forth on Christmas day itself so we need to work out the number of days between today and the 24th December.
 |
| Not long to go! |
We want our calculation to show the number of dates automatically as time passes so rather than type-in today's date as a constant it is best to use Excel's TODAY function which always returns the current date.
In the illustration 24/12/2014 is entered into cell A1 and the formula =TODAY( ) is entered into cell A2. Don't forget the brackets for the function, nothing is required inside them but they have to be there for Excel to recognise the function call.
When you make your entry you only need type the first, opening bracket, the formula is not case sensitive so it is quite acceptable to just type-in "=today(". The final job is to calculate the difference in days between the two dates, for this we enter a simple formula in another cell to minus the earlier date from the later date, in cell A3 the formula is =A1-A2.
Come back tomorrow, the difference will one day less and you still haven't done your cards. Have you?
What day of the week is Boxing Day?
Now, I'm quite a sharp fellow and I know that Boxing day is the 26th December, the day after Christmas day. I even used to know why it is called Boxing day but what I don't know is what day of the week it is. No problem. Firstly enter 26/12/2014 into a convenient cell, then right-click and choose Format Cells from the shortcut menu. Click the Number tab and then Custom at the end of the category list. Don't go to the Date category as Excel does not have a standard format to show the day of the week, instead we are going to create one.
 |
| Formatting dates using a Custom Number format code |
Date Format Codes
Excel date format codes are made up of combinations of d, m and y codes for day, month and year values using as many or as few as you need. For example, d gives you today's date as significant digits (1 as 1, 26 as 26), dd gives you today's date as two digits (1 as 01, 26 as 26), ddd gives you the short day of the week i.e. Fri and dddd gives you the full day of the week i.e. Friday.
Use the existing date codes as a pattern and examine the results of your format code in the Sample box as you type the code elements into the Type box. You can always re-use any format codes that you have already created in this workbook—they are stored at the end of the Custom list.
Enter your dddd code, click OK and now your cell shows that, this year, Boxing day is on Friday.
For your homework tonight you have to:Enter your dddd code, click OK and now your cell shows that, this year, Boxing day is on Friday.
The Excel Christmas Date Challenge
- Assuming you've already received your December salary and blown it. Calculate how many days you have to survive until your next pay day on 30th January 2015.
- Work out on which day of the week you were born.
Remember that it goes like this:
Monday's child is fair of face,
Tuesday's child is full of grace,
Wednesday's child is full of woe,
Thursday's child has far to go,
Friday's child is loving and giving,
Saturday's child works hard for a living,
And the child that is born on the Sabbath day is bonny and blithe, and good, and gay.
Does the old rhyme work for you? I was born on a Friday and while I have been called many things I don't remember it ever being loving or giving.
You gave me a really rotten cold many years ago at Lectern for which I thanked you kindly.
ReplyDelete