Dealing with dates in Excel is easier than you think.
For a start, dates are just numbers. The number 1 is 1 Jan 1900, 2 is 2 Jan 1900, and so on – we’re now over 40000. (In the help files and function descriptions, they are referred to as ‘serial numbers’). That means that you can add and subtract dates, or move along a calendar by adding to/subtracting from a date.
Secondly, if you want to know day of the week, month of the year, or even number of working days between two dates, there’s a function to do it. Excel has a whole range of date functions built in, to make your life easier.
And thirdly, the date you see is just the serial number, formatted in a particular way. If you want to see the day of the week, change the number formatting. If you only want to see the day number, change the number formatting…
In this tutorial, we perform a few date calculations and use some of the more common date functions to build a perpetual calendar in Excel – just enter the month and year, and it will display the day numbers in the familiar Sunday-Saturday calendar grid.
If you want to build a 12-month calendar from just the year, take a look here (assumes you understand the concepts described in the tutorial on this page).[youtube https://www.youtube.com/watch?v=nyvtre4MXgI&hl=en&fs=1]
