Tuesday 3 September 2013


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.

1 comment:

  1. However, this research has helped to detail what non-problematic "positive play" seems like. This "carrot" approach (i.e. play like this and you extra probably to|usually tend to} have a positive experience) might resonate with a wider range of players who ignore messages that focus on on} downside gambling, 코인카지노 which they see as largely irrelevant to them. The Salvation Army engages in advocacy with governments, commercial entities and people concerning the inherent dangers of gambling and the struggling it inflicts on gamblers and their households. Gambling just isn't merely a innocent exercise but can become a compulsive dependency.