In this tutorial, we will explain date function of Excel which is critical in working dates in worksheets.
Microsoft Excel provides a function of Date and Time which is helpful in data analysis where it requires a specific time or period. As you go over this time we are going to summarize the gained knowledge that will add skills to manipulate date that are best suited to calculations of dates.
To begin with let’s define what this date function…
What is Date Formula Excel
The Date function is under the date and time functions of Excel. Specifically, it is used to calculate dates in a worksheet.
Additionally, date function is very useful when it comes to financial analysts which needs financial modeling for a specific period of time. Like for instance, analyst use this function to dynamically link the year, month, and day from different cells into one function.
Apparently, this function is versatile where in you can easily assemble date using year, month, and day values that came from results or cell references. Apart from this is useful in providing inputs in other functions like SUMIF or COUNTIFS,
date formula in excel
The syntax of this function is as follow:
=DATE(year,month,day)
The DATE function includes the following arguments:
- Year – This is a required argument. The value of the year argument can include one to four digits. This is a required argument which is up to four-digit format. The year argument reads the date system of the computer you are using.
- Month – This is a required argument which could be a positive or negative integer as long as it is from 1 to 12 as January to December.
- Day – This is a required argument. It can be a positive or negative integer representing the day of a month from 1 to 31.
- If the day is greater than the number of days on the specified month of the year, the day will add to the number of days on the first day of the month.
- On the other hand, if it is less than 1, the function will subtract the number of days, and plus one from the first day of the specified month.
How to Use the DATE Function in Excel?
This time we will work with the Date function, which could be output as a formula. Below are examples where you can use the Date function for better understanding.
Get a month from the date
To get the month from the date we use the month function which is represented by the Date function. The month function in the formula will obtain the month index from the Date function. For intance the formula entered in cell J2 returned the month 12.
Observe the formula and image below:
=MONTH(DATE(2022,12,28))
Find out a leap year
In finding leap year the Date function will automatically adjust the format to year values out of range. In the innermost part of the formula is the year which has the parameters that indicate the input data. Wherein 2 is the indicator of the month of February and 29 is the day so this will return 2/29/2022. Observe the formula below:
=MONTH(DATE(YEAR(I2),2,29)) = 2
When it is a non-leap year, DATE function will return the date as March 1, because there is no 29th day in February.
Moreover, the MONTH function will pull the month from the output, such as 2 for the leap year and 3 for non-leap year.
Further, the constant is 2 is the basis of the result. If the month is 2 the result will return True otherwise the Date formula will return False.
Take a look at the figure below:
Highlight a set of dates
A conditional formatting rule is applied to column I in this DATE function example. The dates greater than 2022/12/12 are highlighted using red format style. So, as shown in the screenshot, four dates greater than the specified date are highlighted in the configured format. The other dates that do not satisfy the criteria are left unformatted as no rule applies to such dates.
excel formula for date range
To set date range in Microsoft Excel we calculate it in a list of dates. The thing we should do first is format cells which to have a start and end of a date. Thus utilize the operators + or – to know the duration of date or range.
Assuming you have two dates which are placed in columns A and B. In creating date range you can refer on this formula along with the Text function:
=TEXT(A2,”mm/dd”)&” – “&TEXT(B2,”mm/dd”)
excel formula if date is greater than or Less than another date
This time we will see if the date values from one column or another column are greater than or less than the date on each value. This will be obtained with the greater than “>” or less than “<” operator in Excel.
Follow the following steps to see how it works:
Step 1: In cell J2, type formula as” =H2>I2”. Obviously, the formula will check if the date value under cell H2 is greater than the date value under cell BI2 or not. If it is, Excel will yield a Boolean output as TRUE. Otherwise, it will generate a result as FALSE.
Step 2: Drag the formula across rows to get the output for all cells in column J.
Step 3: Also, you can check whether date values from column H are lesser than those of column I. All you need to do is replace the Greater Than (“>”) operator with Lesser Than (“<”) operator in the formula used in the previous step. See the figure below in cell J2.
Step 4:Drag the formula across cells in column J to get the desired output.
formula to remove time from date in excel
There are various formulas you can remove time from date these are INT, DATEVALUE, and TEXT.
Conclusion
In conclusion, the Excel DATE function creates a valid date from the individual year, month, and day components. The DATE function is useful for constructing dates that need to change dynamically based on other values in a worksheet.
If you have more suggestion and clarification feel free to leave comments below.
Thank you for reading 🙂