Happy Friday Excellers time for more #formulafriday fun. Today let’s look at using the DAY() function.
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.
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. Excel stores all date as integers representing the number of days since 1900-JAN-00 and times as decimal 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.
How Excel Interprets Dates.
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.
You can see this if you format a worksheet cell that contains a date as General. I have the same date of 31 January 2011 in my both cells, one is formatted as a short date and one as General.
Let’s Concentrate On The DAY() Function Today.
The DAY() Function returns the day of the month as a number between 1 and 31 when given a date. The Syntax is really simple
Where Date is a valid Excel date in serial number format.
Some Examples Of The DAY() Function
1. Extract The Day From A Date
This is a straightforward use of the DAY() Function. As it only takes one argument (date) the day of the date is extracted from the date contained in column B.
2. Calculate The Number Of Days In A Month Based In On The Month.
If you have the month number, then you can use the DAY Function to calculate how many days are in the month.
In the above formula, the DATE function returns the first day of the following month, from which you subtract 1 to get the last day of the month you want. And then, the DAY function converts the date to a day number.
3. Calculate The Number Of Days In A Month Based On A Date
We can combine the DAY Function with the EOMONTH Function to get the last day of a month. Which gives you the number of days in a month.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.