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.

devender says

i want to convert 412 days to like this

1 years, 1 month, 17 days

Barbara says

Hello Devenfer, do you have a start adate and end date for the 412 days?. Each month may not have the same number of days, and year length can differ is a leap year?

Regards

Barbara