Happy Friday Excellers time for more #formulafriday fun. Today let’s look at using the DAY() function. In Excel, the DAY function calculates the day of the month for a given date. This function is useful for determining deadlines or other time-sensitive information. Have a look at your spreadsheets and see how many contain dates on them as well as calculations between dates. In this blog post, we’ll show you how to use the DAY function in Excel and provide some examples. Stay tuned!
How Excel Uses 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 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.
Excel Date Examples.
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 In Excel 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
THe DAY() Syntax
=Day(Date)
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 every month and receive my free Ebook, 50 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.
How To Excel At Excel – Formula Friday Blog Posts.
