Tuesday, 3 September 2013

Dates

By , Director, Naturally Sales Ltd.


We'll take a little look at date formats today. There are occasions for all of us when Excel's dates don't perform as we'd like them to. By understanding a little more about how Excel processes dates, we're better-equipped to solve any problems we might find.

When you see a date in Excel, what you're actually seeing is just a number, formatted to look like a date. The number represents the number of days since 31st December 1899 - it seems that spreadsheets were invented by the Victorians! If you re-format a date cell as "General" or "Number", you'll see the date changes to a 5-digit number, probably beginning with a 4. Today is 41520 - that's the number of days which have elapsed since the turn of the last century!

That dates are stored as numbers is both a help and a hindrance to us as users - it means we can add or subtract dates as we would any other number, which allows us to measure results over time. It also means that although we see, for example, a year in the date, we can't just change the formatting to get at this information accurately - even if we format as year only, Excel is seeing the exact date.

Fortunately, there are functions we can use to extract information from a date. They are:

=DAY(cell ref)
- gives the day within a month
=MONTH(cell ref)
- gives the month number within a year
=YEAR(cell ref)
- gives the year


Using these functions, we can extract anything we want from a date.

Exercise: have a play with the WEEKDAY function and see what it does - any questions, just email, from our website.

No comments:

Post a Comment