Most Excel workbooks will use dates in them at some point. This could be to calculate the days left on a project, worked on a project activity or display dates of transactions on an invoice for your monthly Sales figures. Have a look at your spreadsheets and see how many contain dates on them as well as calculations between dates.
In order to harness the full potential of the date and time functions of Excel you really need to know how Excel deals with dates and times.
Let’s concentrate on dates.
The first thing we need to understand is no matter how you format the date using custom formatting like DDD/M/YYYY or MM/YY, Excel always internally displays them the same way. This can manifest itself as an ‘issue’ sometimes when analyzing data. An example would be in a Pivot Table.
Let’s say you want to see sales of Widgets by month, and you have various daily invoice dates within your months, but the pivot table displays them grouped by the invoice daily dates in the month rather than just by monthly sales. You have formatted your invoice dates as MMM/YY, but it STILL analyzes them the same way. Excel is behaving as expected believe it or not, so here’s why.
Excel stores all date as integers representing the number of days since 1900-JAN-00 and times as decimals fractions- a fractional portion of a 24 hour day. In this way the dates and times can be added, subtract and compared just like any other numbers.
Let’s take the date 31 January 2011. To Excel it’s actually as 40574 as it is that many days since 01-JAN-1900. So, even if you format the cells as MMM/YY Excel still works the date out internally as the number of days since 01-Jan-1900.
Here is a solution to this using the DAY Function. Enter a new column in your spreadsheet and call it Month- or any name of your choosing, it’s simply a ‘helper’ column.
We use the DAY() function which will tell us the number of days that have elapsed in the current month.
- So for 02 December 2011 the DAY() function returns 2.
- Now we can subtract the number of days elapsed from the date, and then add 1.
- This will always give the first of the month.
- All we need to then do is format the cells in the MMM/YY. This will now give us the display we need for Excel to analyze our sales data truly by Month.
An example would be a date like 02/05/2011 in cell A2. So, we type in cell C2
to give 01/05/2011- then reformat with MMM/YY.
Once you understand how Excel interprets and calculates Dates and it’s logic, working with then becomes much simpler and straightforward.