There are many functions in Microsoft Excel that may be used to work with dates and timings in Excel. Each function completes a straightforward task, but by combining numerous functions into a single formula, you may handle trickier and more complicated problems. The purpose of discussing DATE functions in Excel is to help different people perform more complex and challenging tasks by combining several functions within one formula.
The DATE function is used to calculate dates in Excel. To work with these formulas you first need to insert the dates in Excel. Excel provides different functions to work with dates & times such as TODAY, NOW, WEEKDAY, EOMONTH, etc. which we will discuss here with examples.
Excel DATE function List
Get the Current date and Time
- TODAY- It returns today's date.
- NOW- It returns the current date and time.
Converts Dates to / From text
- DATEVALUE- Helps to Convert date in the text format to date format
- TEXT- It helps to convert a date to a text value.
Retrieve dates in Excel
- DAY- It returns the day of the month
- MONTH- It is used to return the month of a specified date
- YEAR- It is used to return the year of a specified date
- EOMONTH- It returns the last day of the month.
- WEEKDAY- It is used to return the day of the week.
- WEEKNUM- It is used ti return the week number of a date.
Calculate date difference
- DATEDIF- It is used to return the difference between two dates
- EDATE- It is used to return a date N months before or after the start date.
- YEARFRAC- It is used to calculate the fraction of the year between 2 dates.
Calculates Workdays
- WORKDAY- It returns a date N working days in the future or in the past.
- WORKDAY.INTL- It returns a date N weekdays from the start date with custom weekends
Excel DATE Function
It will return the date in serial number based on the year, month, or day value as provided.
Syntax:
DATE(year,month,day)
Arguments:
- Year - This argument includes - 1 to 4-digit values. Excel understands this 'year' argument according to the date system of the local computer which we use. For example- Excel windows use the 1900 date system by default which means DATE (21,2,6) gives the result as 06-02-1921.
- Month - This argument includes a positive or negative integer that represents the month of the year from January to December.
- Day - This argument also includes a positive or negative integer representing the day of the month from 1 to 31.
Example 1
Example 2
It will return on the first day of the current year & month.
Example 3
Excel TODAY Function
The TODAY() function name suggests it will return today's date, and it has no arguments.
Syntax:
TODAY()
Example1
Here we will print the current date and also add 10 days to the current date.
Example 2
To add 10 days to Today's date.
Excel NOW Function
This function returns the current date as well as the time & doesn't have any arguments.
Syntax:
NOW()
Example 1
Excel DATEVALUE Function
It converts the date in text format to a serial number, which can be represented as a date.
Syntax:
DATEVALUE(date_text)
Arguments:
1. date_text - This argument is a text that represents the date in Excel date format.
Example 1
Excel TEXT Function
It converts any numeric value not only dates to a text string. Through this function, we can change the date to text strings in a variety of formats.
Syntax:
TEXT(value,format_text)
Arguments:
1. value: The value that is to be converted.
2. format_text: The format in which you want to output the date value.
These are the different formats used in the TEXT function to change dates to text strings.
Example 1
Example 2
Example 3
Excel DAY Function
It returns the day of a month, i.e. integer from 1 to 31.
Syntax:
DAY(serial_number)
Arguments:
1. serial_number: This value represents the day of the month you want to find. E.g: 5th day of June
Example 1
Example 2
The DAY(TODAY()) function returns the day of today's date, as shown below:
Excel MONTH Function
This function returns the month of the given date as an integer from 1 to 12 (January to December).
Syntax:
MONTH(serial_number)
Arguments
1. serial_number: This value represents the date for which you want to find the month.
Example 1
The MONTH(TODAY()) function returns the month of today's date.
Excel YEAR Function
It returns the year of a specified date.
Syntax:
YEAR(serial_number)
Arguments
1. serial_number: The date to be specified.
Example 1
Example 2
Example 3
Excel EOMONTH Function
This function returns the last day of the month after adding a specified number of months to a given date.
Syntax:
EOMONTH(start_date,months)
Arguments:
1. start_date: In this argument, the date should be written in date format, not in the text.
2. months: In this argument, if a positive integer is given then corresponding months can be added to the start date & if a negative integer is given then the corresponding months can be subtracted from the start date.
Example 1
Example 2
Example 3
The EOMONTH(TODAY(),0) function returns the last day of the current month.
Excel WEEKDAY Function
This function returns the day of the week as a number from 1 to 7 (Sunday to Saturday) according to the specified date.
Syntax:
WEEKDAY(serial_number,return_type)
Arguments:
1. serial_number: It can be a date or the cell that contains the date.
2. return_type: It is optional, as it specifies which day should be considered as the first day of the week.
NOTE: 1st day of the week is by default Sunday.
Example 1
Example 2
In the below example, 2 is given as return_type i.e. Monday is referred to as the first day of the week.
Example 3
Here the day of today's (01-04-2021) date is the result & the default value (Sunday) is considered here because no return_type is given.
Excel DATEDIF Function
This DATEDIF function calculates the difference between two dates in days, months, or years. For calculating the difference b/w dates which time interval should be used depends on the letter which we specify in our last argument i.e. at the unit.
Syntax:
DATEDIF(start_date,end_date,unit)
Arguments:
1. start_date: The start date for evaluating the difference.
2. end_date: The end date for evaluating the difference.
Example 1
Example 2
Example 3
Here "m", "y","d” mean month, year & date. In the first example, the difference between dates is calculated by months, second by year & third by date.
Excel WEEKNUM Function
It returns the week number based on the specified date, i.e. from 1 to 52 weeks of the year.
Syntax:
WEEKNUM(serial_number,firstday_ofweek)
Arguments:
1. serial_number: This is the date for which we want the week number.
2. firstday_ofweek: This is an optional argument that specifies which numbering system should be considered & which day of the week can be treated as the start of the week, Default(omitted) is 1. The table below is the parameters that can be given in firstday_ofweek argument.
First Day of the Week Start Table:
| 1 | Sunday | 1 |
| 2 | Monday | 1 |
| 11 | Monday | 1 |
| 12 | Tuesday | 1 |
| 13 | Wednesday | 1 |
| 14 | Thursday | 1 |
| 15 | Friday | 1 |
| 16 | Saturday | 1 |
| 17 | Sunday | 1 |
| 21 | Monday | 2 |
Example 1
Example 2
Example 3
In the below example,21 is given as the second argument which means Monday is taken as the first day of the week & in the above example, the result shown is 15 but taking 21 as the first_dayofweek means Monday is the first day, the result is 14.
Excel EDATE Function
This function adds or subtracts the specified month to a given date.
Syntax:
EDATE(start_date,months)
Arguments:
1. start_date: This is the initial date on which the months are added or subtracted.
2. months: This is the number of months which is to be added or subtracted in the specified date.
Example 1
Example 2
Example 3
Excel YEARFRAC Function
This function returns the fraction of the year which represents the number of whole days between the start & end date.
Syntax:
YEARFRAC(start_date,end_date,[basis])
Arguments:
1. start_date: This is the start date in the serial number.
2. end_date: This is the end date in the serial number.
3. basis: This is the optional argument that specifies the day count method.
|
0(default) | US 30/360 |
| 1 | actual/actual |
| 2 | actual/360 |
| 3 | actual/365 |
| 4 | European 30/360 |
Example 1
Using someday count methods.
Example 2
Example 3
Excel WORKDAY Function
This function helps if we exactly know how many working days we have & want to find out the date when the number of working will skip. This function always includes working days & excludes weekend days.
Syntax:
WORKDAY(start_date,days,holidays)
Arguments:
1. start_date: This argument is the date from which the counting of weekdays begins. Excel doesn't include start_date as a working day.
2. days: This is the number of working days.
3. holidays: This is an optional argument. If the days mentioned include any holidays, then we need to make a list of holidays separately for this and mention it here.
Example 1
28 workdays from the start date, excluding holidays.
Example 2
28 workdays before the start date, excluding holidays
Example 3
28 workdays from the start date, no holidays.
Example 4
28 workdays from today's date, no holidays.
Excel WORKDAY.INTL Function
This is a modification of the WORKDAY function as it provides a custom weekend parameter that distinguishes this from the WORKDAY function.
Syntax:
WORKDAY.INTL(start_date,days,[weekends],holidays)
Arguments:
1. start_date: This argument is the date from which the counting of weekdays begins. Excel doesn't include start_date as a working day.
2. days: This is the number of working days.
3. holidays: This is an optional argument. If the days mentioned include any holidays, then we need to make a list of holidays separately for this and mention it here.
4. weekends: Through this argument, we can specify which days of the week to be treated as non-working days, either by weekend number or specific character string.
Weekend Number
|
1 (default) | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday |
| 12 | Monday |
| 13 | Tuesday |
| 14 | Wednesday |
| 15 | Thursday |
| 16 | Friday |
| 17 | Saturday |
If this weekend argument is blank in this function, then it will automatically take the combination of Saturday & Sunday.
For instance:
- "0000011"-Saturday & Sunday are weekends(non-working days)
- "1000010"-Monday & Saturday are weekends(non-working days)
Example 1
30 days from the start date, excluding holidays & Sundays, and Monday as weekends (by giving weekend number 2 as arguments).
Example 2
30 days from the start date, excluding holidays & Sunday, Monday as weekends(by giving weekend string "1000001" as arguments).
Example 3
20 days from the start date, no holidays & Monday, and Saturday as weekends (by giving the weekend string "1000010" as arguments).
Excel NETWORKDAYS Function
This function returns the number of working days between two dates, excluding weekends & holidays as optional arguments.
Syntax:
NETWORKDAYS(start_date,end_date,holidays)
Arguments:
1. start_date: The initial date to start the evaluation.
2. end_date: The last date to end the evaluation.
4. holidays: Used to specify holidays.
Example 1
Example 2
Excel NETWORKDAYS.INTL Function
This function also returns the number of working days between two dates but provides the additional argument weekend to specify which days should be counted as weekend days.
The structure of the weekend argument is the same as for WORKDAY.INTL i.e. we can use either the weekend number or character string.
Syntax:
NETWORKDAYS.INTL(start_date,end_date,[weekend],holidays)
Arguments:
1. start_date: The initial date to start the evaluation.
2. end_date: The last date to end the evaluation.
3. weekend: Use to specify the weekends.
4. holidays: Used to specify holidays.
Example 1
Here, the weekend argument is given in the form of a number.
Example 2
Here, the weekend argument is given in the form of a character string of 0's & 1's.
Hopefully, this extensive overview of Excel's date functions has given you a general idea of how date formulas in Excel operate. I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!
How to get DD MMM YYYY in Excel?
Follow the below steps:
Step 1: Select the cells that contains the date
Step 2: Right click on and select Format cells
Step 3: Select custom in the Number Tab>Type' dd-mm-yyyy' in teh Type text box
Step 4: Click ok
What are the Top DATE Functions in Excel?
Below is the list of Top Used DATE function in Excel:
NOW- It returns the current date and time.
DAY- It returns the day of the month
MONTH- It is used to return the month of a specified date
YEAR- It is used to return the year of a specified date
DATEDIF- It is used to return the difference between two dates
YEARFRAC- It is used to calculate the fraction of the year between 2 dates.
WORKDAY- It returns a date N working days in the future or in the past.
How date can be Formatted in Excel?
Follow the below steps to format dates in Excel:
Step 1: Select the range of cells containing the dates.
Step 2: Right click and choose "Format cells".
Step 3: In the Format Cells dialog box,go to the "Number " tab and select "Date" form the category list.
Step 4: Choose the desired date format from the available options or customize the format using the provided options.
Step 5: Click "ok" to app;y the formatting to the selected cells.
Similar Reads
Excel Date and Time Formats With Examples
Excel has a built-in time feature that is simple to use and can save you a lot of time. We can insert current Data and Time into a worksheet cell using Excel's built-in functions. When you enter a date or time into a cell, the date and time are displayed in the cell's default date and time format. T
4 min read
Excel SUMIF Function: Formula, How to Use and Examples
The SUMIF function in Excel is a versatile tool that enables you to sum values based on specific criteria, such as a true or false condition. This feature makes it perfect for conditional summation tasks like totaling sales over a particular amount, adding up expenses in specific categories, or summ
7 min read
How to use IRR Function in Excel: Formula + Examples
How to Calculate Internal Rate of Return in Excel: Quick StepsEnter Your Cash FlowsEnter IRR Formula >> Press Enter Preview ResultsEver wondered how businesses decide which projects are worth investing in? The Internal Rate of Return (IRR) is a key financial metric that helps evaluate the prof
8 min read
How to Combine Excel Functions in a Formula?
We all use Excel for some small computations in data but excel also provides the functionality to combine these small functions and make a formula for manipulating the data. For example, let's say that in school we need to increment the ID numbers of students by a number but the ID's have some text
2 min read
Exponential Functions: Definition, Formula and Examples
Exponential functions are mathematical functions in the form f(x) = a â‹… bx, where: a is a constant called the coefficient, which scales the function but does not change its exponential nature.b is the base of the exponential function, which must be a positive real number other than 1.x is the expone
10 min read
OFFSET Function in Excel With Examples
Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range
4 min read
Excel ROWS and COLUMNS Functions with Examples
In Microsoft Excel, where precision and efficiency reign supreme, mastering the art of maneuvering through cells and worksheets is indispensable. Two indispensable components in your Excel toolbox are the ROW and COLUMN functions, each meticulously crafted to execute discrete functions that can subs
7 min read
How to Create a Data Entry Form in Excel With Examples
How to Make Data Entry Form in ExcelStep 1: Select Any Cell in Your Dataset > Press the Ctrl+T keys together to Convert it to a table. Step 2: Now Click on the Form Button in the Ribbon Step 3: Pressing the Form button generates a data entry form for your table, featuring fields corresponding to
9 min read
Excel COUNTIF Function for Exact and Partial Match (With Examples)
The COUNTIF function in Excel is a powerful tool used to count cells that meet specific criteria, whether it's an exact match or a partial match. This function is incredibly useful for managing large datasets, analyzing trends, or summarizing data quickly. For example, you can use COUNTIF to count h
8 min read
Statistical Functions in Excel With Examples
To begin with, statistical function in Excel let's first understand what is statistics and why we need it? So, statistics is a branch of sciences that can give a property to a sample. It deals with collecting, organizing, analyzing, and presenting the data. One of the great mathematicians Karl Pears
6 min read