What is DAY Function in Google Sheets?
The Day function in Google Sheets is used to extract the day component from a supplied date. The argument we give the function is a valid date and it returns an integer representing the day of the month.
As explained before, the DAY function returns the day from a given date. For example, the DAY formula is helpful when finding the daily revenue in a month. The function returns the day of the month that a specific date falls on and varies from 1 to 31. Look at the date supplied below and see how it shows the day of the month.
Key Takeaways
- The Day function in Google Sheets is used to extract the day component from a date.
- It takes a date or cell reference as an argument and returns an integer representing the day of the month.
- The day which is displayed does not include the time component.
- It cannot handle invalid dates, so it’s essential to validate dates before using the function.
- The DAY function is a simple but powerful tool in Google Sheets for extracting the day of the month from a given date and returns a value between 1 and 31, corresponding to the day of the month.
Syntax
We can go ahead in detail in the article to check how the DAY formula is used in different scenarios in Google Sheets. Let us first look at the DAY formula in Google Sheets.
=DAY(date)
Where:
date: The date from which you want to extract the day. If the sate is given directly, it should be enclosed within double quotes.
The function returns an integer between 1 and 31 that represents the day of the month. For example, if the date is March 30, 2023, the DAY function will return 30.
You can also enter a cell reference as an argument. For example, to extract the day from the date in cell A2, you enter the formula as follows:
=DAY(A2)
How to Use DAY Function in Google Sheets?
As seen earlier, using the DAY function in Google Sheets is used to extract the day from a given date. The day is returned as a numeric value between 1 and 31. Now, let us look at how to use this function. There are two ways to enter the function.
Entering DAY in Google Sheets manually
- Through the Google Menu bar
- Entering DAY in Google Sheets manually
We have a few dates on Google Sheets as shown below.
Step 1: Now, enter the following formula in B1.
=DAY(A1)
Step 2: Press Enter. Check how the day is displayed for that particular month.
Step 3: Drag the formula till B3 to get the days for all the other dates.
Using the Google Menu bar
- We can also enter the same function using the Google Menu bar.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Date” ➝ “Day.”
- Enter the required argument. Close the bracket and press the “Enter” key.
Examples
The DAY function needs a single argument: a date or a cell reference of a cell containing a date. Let us look at some interesting examples on how to use the DAY function in Google Sheets
Example #1 – Using DAY function to extract the Day from a Date
We have the data of some children born in a hospital. To get an idea on which day of a month each child is born for some internal statistics, let us see how the DAY function can be used.
Look at the details below.
Step 1: Let us find which day of the month each date corresponds to. For this apply the following formula in cell B2.
=DAY(A2)
Step 2: Press Enter. Drag the formula all the way down to cell B11.
You can see how the day has been extracted from each date.
The DAY function extracts the day from each date as an integer value. It works with valid date formats, irrespective of if you input them manually or use it with other date functions.
Example #2 – Using DAY function to filter Data Based on Specific Days
Let us have the details of some employees who have worked different days of a week. Let us try to filter data for how many employees worked on the 18th day for an extra bonus.
The details are given below.
Step 1: Let us find which day of the month each date corresponds to. For this apply the following formula in cell B1.
=DAY(A1). Press Enter.
Step 2: Drag the formula all the way down to B10.
Step 3: To filter the employees who worked on day 18th, we use the following function
=FILTER(A1:B10,B1:B10=18)
Here, we specify the range from which we want the values and the condition in the second argument.
Press Enter. We can see that 2 employees worked on the 18th day.
Example #3 – Using DAY function to identify Weekday Days (e.g., 1st, 15th, 30th)
We have a list of dates in Google sheets. Let us use them to identify specific weekdays such as the 1st, 15th, 30th day of the month. Look at the dates below.
Step 1: Now, let us apply the following function in cell B1.
=OR(DAY(A1) = 1, DAY(A1) = 15, DAY(A1) = 21)
Here, we use the OR function along with DAY to find if the day is either the 1st, 15th or 21st day of the month. Press Enter.
Step 2: Since it the first day, we get a TRUE. Drag the formula across for the remaining dates.
Thus, we use the DAY() function to extract the day from a date. Combine DAY() with a logical function like OR() to check for specific days (1st, 15th, 30th).
Example #4 – Using DAY function to create Day-Based Conditional Formatting
As an extension of the previous example. Let us apply the same format to get conditional formatting for the above days;1st, 15th and 21st. We have the table as shown below.
Step 1: Go to Format -> Conditional Formatting.
Step 2: Now, in the Conditional Format Rules pane on the right, apply the range. Choose the option “Custom formula is.”
Step 3: Enter the following formula:
=OR(DAY(A1:A10) = 1, DAY(A1:A10) = 15, DAY(A1:A10) = 30).
Step 4: Choose a formatting style. For example, (e.g., background color).
Step 5: Click Done.
Step 6: Press Enter. You can see the cells with the 1st, 15th or 30th days are highlighted.
Thus, you can use the DAY function along with conditional logic to identify if the day of a given date is one of the days you need.
Important Things to Note
- If dealing with time components when using the DAY function, use DATEVALUE to remove the time portion before applying the Day function.
- Remember that the DAY function operates based on the system’s local time.
- The Day function extracts the day of the month, not the day of the week. For the day of the week, you can use the WEEKDAY function.
- The Day function is meant for date values, it does not involve TIME.
Frequently Asked Questions (FAQs)
The DAY function can be combined with other formulas for more complex calculations. For example, we can use the IF and DAY functions as =IF(DAY(A1)=10, “tenth day”, “Other”) checks if a date is the 10th and returns “tenth day. Ït can also be used in combination with other DATE functions such as:
MONTH(): To extract the month from a date.
YEAR(): To extract the year from a date.
TEXT(): To format dates in specific ways.
DATE() and DATEVALUE(): To create or convert dates.
TODAY() and NOW(): To get the current date and time.
DATE function? You can combine the DAY function with DATE to extract the day from a specific date. For example, to get the day of the month for April 10th, 2024:
=DAY(DATE(2024, 4, 10))
The DAY function always returns a numeric value between 1 and 31. However, you can change how the date appears in Google Sheets by formatting the cell using Format > Number > Date or using the TEXT function if you need a specific format for the entire date.
Download Template
This article must help understand DAY Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Gantt-Chart-Google-SheetsDownload
Recommended Articles
Guide to What Is DAY Function in Google Sheets. We learn its syntax & how to use it to return the day from a given date with examples. You can learn more from the following articles. –
Leave a Reply