How to get excel to display the day of the week and month |
 |
What Day of the Week were you Born on?
Formatting dates in excel is fun. Let's play a little game... Do you know what day of the week you were born on? Excel does! Try this...
1. Type your date of birth into a cell. The easiest way to type a date is to use the calculator pad on the right of your keyboard and use the following format date/month number/year e.g. 27/6/61 (for 27 June 1961)
2. Now click the green tick at the top (see figure d) to enter it into the cell. Notice the year will change to a 4 figure year (e.g. 1961)
3. Right click on the cell with your date in it and select Format Cells
4. Select Custom in the list on the left.
5. Now look at the right hand side where it says Type: Delete the letters dd/mm/yyyy (so it is blank)
6. Now type dddd
It should look like this...
7. Finally click the OK button and you should see what day you were born on
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 its living;
But the child that is born on the Sabbath day
Is bonny and blithe, and good and gay.
So can you display a date in other ways?
Yes, you can flexibly format the date how you wish according to these rules:
d = single character date (e.g. in my example 27)
dd = two character date (e.g. still 27 - but if it was the 7th it would display 07)
ddd = three letter day of the week (e.g. Tue)
dddd = full name of day (e.g. Tuesday)
m = single character month (e.g. in my example 6)
mm = two character month (e.g. now 06)
mmm = three letter name of the month (e.g. Jun)
mmmm = full name of month (e.g. June)
yy = two figure year (e.g. 61)
yyyy = four figure year (1961)
Therefore if you type dddd d mmmm yyyy it will display as: Tuesday 27 June 1961
Now you can just type another family members' birth date in that cell using the number keypad as at step 1 and 2 and see what day they were born on.
Tip: I often format dates as d mmm yy (1 Aug 23) so it's a neat short way of displaying dates much more clearly and are not confused with American formats.