1. DATE Function
DATE function returns a valid date based on the day, month, and year you input. In simple words, you need to specify all the components of the date and it will create a date out of that.
Syntax
DATE(year,month,day)
Arguments
- year: A number to use as the year.
- month: A number to use as the month.
- day: A number to use as a day.
Example
In the below example, we have used cell references to specify the year, month, and day to create a date.
You can also insert arguments directly into the function to create a date as you can see in the below example.
And in the below example, we have used different types of arguments to see the result returned by the function.
2. DATEVALUE Function
DATEVALUE function returns a date after converting a text (which represents a date) into an actual date. In simple words, it converts a date into an actual date which is formatted as text.
Syntax
DATEVAUE(date_text)
Arguments
- date_text: The date which is stored as a text and you want to convert that text into an actual date.
Example
In the below example, we have inserted a date directly into the function by using double quotation marks. If you skip adding these quotation marks it will return a #NAME? error in the result.
In the below example, all the dates on the left side are in textual format.
- A simple textual date that we have converted into a valid date.
- A date with all three components (Year, Month, or Day) in numbers.
- If there is no year in the textual date, it will take the current year as the year.
- And if you have a month name is in alphabets and no year, it will take the current year as a year.
- If you don’t have the day in your textual date it will take 1 as the day number.
3. DAY Function
DAY function returns the day number from a valid date. As you know, in Excel, a date is a combination of day, month, and year, DAY function gets the day from the date and ignores the rest of the part.
Syntax
DAY(serial_number)
Arguments
- serial_number: A valid serial number of the date from which you want to extract the day number.
Example
In the below example, we have used the DAY to simply get the day from a date.
And in the below example, we have used DAY with TODAY to create a dynamic formula that returns the current day number and it will update every time you open your worksheet or when you recalculate your worksheet.
5. DAYS Function
DAYS function returns the difference between two dates. It takes a start date and an end date and then returns the difference between them in days. This function was introduced in Excel 2013 so not available in prior versions.
Syntax
DAYS(end_date,start_date)
Arguments
- start_date: It is a valid date from where you want to start the days’ calculation.
- end_date: It is a valid date from where you want to end the days’ calculation.
Example
In the below example, we have referred the cell A1 as the start date and B1 as the end date and we have 9 days in the result.
Note: You can also use the subtract operator to get the difference between two dates.
In the below example, we have directly inserted two dates into the function to get the difference between them.
6. EDATE Function
EDATE function returns a date after adding a specified number of months to it. In simple words, you can add (with a positive number) or subtract (with a negative number) months from a date.
Syntax
EDATE(start_date,months)
Arguments
- start_date: The date from which you want to start the calculation.
- months: The number of months to calculate the future or the past date.
Example
Here we have used EDATE with different types of arguments.
- In the first example, we have used 5 as a several months and it has added exactly 5 months on 1-Jan-2016 and returned 01-June-2016.
- In the second example, we have used -1 month and it has given 31-Dec-2016, a date which is exactly 1 month back from 31-Jan-2016.
- In the third example, we have inserted a date directly into the function.
7. EOMONTH Function
EOMONTH function returns the end of the month date which is the number of months in the future or the past. You can use a positive number for a future date and a negative number for the past month’s date.
Syntax
EOMONTH(start_date,months)
Arguments
- start_date: A valid date from where you want to start your calculation.
- months: The number of months you want to calculate before and after the start date.
Example
In the below example, we have used EOMONTH with different types of arguments:
- We have mentioned 01-Jan-2016 as the start date and 5 months for getting a future date. As June is exactly 5 months after January, it has returned 30-Jun-2016 in the result.
- As I have already mentioned, EOMMONTH is smart enough to evaluate the total number of days in a month.
- If you mention a negative number, it simply returns a past date which is the number of months back you have mentioned.
- In the fourth example, we have used a date that is in text format and it has returned the date without returning any errors.
8. MONTH Function
MONTH function returns the month number (ranging from 0 to 12) from a valid date. As you know, in Excel, a date is a combination of day, month, and year, MONTH gets the month from the date and ignores the rest of the part.
Syntax
MONTH(serial_number)
Arguments
- serial_number: A valid date from which you want to get the month number.
Example
In the below example, we have used a MONTH in three different ways:
- In the FIRST example, we have simply used date and it has returned the 5 in the result which is the month number of MAY.
- In the SECOND example, we have supplied the date directly in the function.
- In the THIRD example, we have used the TODAY function to get the current date and MONTH has returned the month number from it.
9. NETWORKDAYS Function
NETWORKDAYS function returns the count of days between the start date and end date. In simple words, with NETWORKDAYS you can calculate the difference between two dates, after excluding Saturdays and Sundays, and holidays (which you specify).
Syntax
NETWORKDAYS(start_date,end_date,holidays)
Arguments
- start_date: A valid date from where you want to start your calculation.
- end_date: A valid date up to which you want to calculate working days.
- [holidays]: A valid date that represents a holiday between the start date and end date. You can refer to a cell, range of cells, or an array containing dates.
Example
In the below example, we have specified 10-Jan-2015 as a start date and 20-Feb-2015 as an end date.
We have 41 days between these two dates, out of which 11 days are weekends. After deducting those 11 days it has returned 30 working days.
Now in the below example with the same start and end dates, we have specified a holiday and, after deducting 11 days of the weekend and 1 holiday it has returned 29 working days.
Again with the same start and end dates, we have used a range of three cells for holidays to deduct from the calculation and, after deducting 11 weekend days and 3 holidays which I have mentioned It has returned 27 working days.
10. NETWORKDAYS.INTL Function
NETWORKDAYS.INTL Function returns the count of days between the start date and end date. Unlike NETWORKDAYS, NETWORKDAYS.INTL lets you specify which days you want to exclude from the calculation.
Syntax
NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)
Arguments
- start_date: A valid date from where you want to start your calculation.
- end_date: A valid date up to which you want to calculate working days.
- [weekend]: A number represents to exclude weekends from the calculation.
- [holidays]: A list of dates that represents the holidays you want to exclude from the calculation.
Example
In the below example, we have used 01-Jan-2015 as a start date and 20-Jan-2015 as an end date. And we have specified 1 to take Sunday – Saturday as the weekend. The function has returned 14 days after excluding 6 weekend days.
Below, we have used the same dates. And I have used 11 in for weekend days which means it will only consider Sunday as a weekend. Along with that, we have also used 10-Jan-2015 as a holiday.
We have 3 Sundays between both dates and a holiday. After excluding all these days the function has returned 16 days in the result. Here in the below example, we have used range to specify holidays. If you have more than one date for the holidays you can refer to an entire range.
Quick Tip: If you want to create a dynamic range for holidays, you can use a table for that. If you want to choose custom days to count as working days or weekends, you can use the below format in the weekend argument.
Here, 0 represents a working day and 1 represents a non-working day. And, seven numbers represent 7 days of the week.
11. TODAY Function
The TODAY function returns the current date and time as per the system’s date and time. The date and time returned by the NOW function update continuously whenever you update anything in the worksheet.
Syntax
TODAY()
Arguments
- In the TODAY function, there is no argument, all you need to do is enter it in the cell and hit enter, but be careful as TODAY is a volatile function that updates its value every time you update your worksheet calculations.
Example
In the below example, we have used TODAY with other functions to get the current month number, current year, and current day.
12. WEEKDAY Function
WEEKDAY function returns a day number (ranging from 0 to 7) of the week from a date. In simple words, the WEEKDAY function takes a date and returns the day number of that date’s day.
Syntax
WEEKDAY (serial_number, [return_type])
Arguments
- serial_number: A valid date from which you want to get the week number.
- [return_type]: A number that represents the day of the week to start the week.
Example
In the below example, we have used a WEEKDAY with TODAY to get a dynamic weekday. It will give you the weekday whenever the current date changes. You can use this method in your dashboards to trigger some values which need to change when weekday change.
In the below example, we have used WEEKDAY with IF to create a formula that first checks the weekday of date and returns a “Weekday” or “Weekend” basis on the value return from WEEKDAY.
13. WEEKNUM Function
WEEKNUM function returns the week number of a date. In simple words, WEEKNUM returns the week number of dates that you specify ranging from 1 to 54.
Syntax
WEEKNUM(serial_number,return_type)
Arguments
- serial_number: A date for which you want to get the week number.
- [return_type]: A number to specify the starting day of the first week of the year. You have two systems to specify the starting date of the week.
Example
In the below example, we have used TODAY with WEEKNUM to get the week number of the current date. It will update the week number automatically every time the date changes.
In the below example, we have added the text “Week-” with the week number for a meaningful result.
14. YEAR Function
YEAR Function returns the year number from a valid date. As you know, in Excel a date is a combination of day, month, and year, and the YEAR function gets the year from the date and ignores the rest of the part.
Syntax
YEAR(date)
Arguments
- date: A date from which you want to get the year.
Example
In the below example, we have used the year function to get the year number from the dates. You can use this function where you have dates in your data and you only need the year number.
And in the below example, we have used today’s function to get the year number from the current date. It will always update the year whenever you recalculate your worksheet.
