This is an overview.
.
Download Practice Workbook
Download the workbook here.
1 – Insert Dates Using the DATE Function
The DATE function is used to create a date based on the provided year, month, and day.
Syntax of DATE function:
Use a value or cell reference to assign year, month and day in the arguments.
The result of the function will be the serial number for that date. Format your cell to display the date: Press Ctrl+1 to change format.
2 – Getting the Current Date
2.1 Using the TODAY Function
The TODAY function is used to return the current date as a serial number.
Syntax of TODAY function:
When you use the TODAY function in a cell, it will automatically update to the current date whenever you open or recalculate the Excel workbook. Format the date: press Ctrl+1 to access Format Cells.
2.2 Applying the NOW Function
The NOW function returns the current date and time as a serial number.
Syntax of the NOW function:
The NOW function automatically updates to the current date and time whenever you open or recalculate the Excel workbook. TODAY only returns the current date, whereas NOW returns both date and time.
3 – Change Dates from/to Text
3.1 Using the DATEVALUE Function
The DATEVALUE function converts a date represented as text into a serial number.
Syntax of the DATEVALUE function:
Dates are stored in Excel as consecutive integers. Only the formatting permits a number to be shown as a date. You can change the serial number back to date format by pressing Ctrl+1.
3.2 Applying TEXT Function
The TEXT function is used to convert a numeric value, date, or time into a text string with a specified format.
Syntax of the TEXT function:
In the format_text argument, choose your formatting.
4 – Get Dates
4.1 Using the DAY Function
The DAY function extracts the day of the month from a given date and returns it as a numeric value.
Syntax of the DAY function:
4.2 Using the MONTH Function
The MONTH function extracts the month from a given date and returns it as a numeric value.
Syntax of the MONTH function:
4.3 Applying the YEAR Funcion
The YEAR function extracts the year from a given date and returns it as a numeric value.
Syntax of the YEAR function:
4.4 Utilizing the EOMONTH Function
The EOMONTH function finds the last day of the output month based on a specified number of months before or after a referenced date.
Syntax of the EOMONTH function:
The start date is January 5, and you want to add 3 months. The function returns April 30.
4.5 Applying the WEEKDAY Function
The WEEKDAY function returns the day of the week for a given date as a numeric value.
Syntax of the WEEKDAY function:
There is an optional argument to specify the numbering system for the days of the week. By default, it considers Sunday as the first day of the week, represented by 1, and Saturday as the last day of the week, represented by 7. The number you enter as the return_type will determine which date should be considered the first day of the week.
4.6 Using the WEEKNUM Function
The WEEKNUM function calculates the week number of a given date based on a specified numbering system.
Syntax of the WEEKNUM function:
The WEEKNUM function can return:
 1: Week 1 specifies the week which contains January 1st;
 2: Week 1 is the week that contains the first Thursday of the year.
4.7 Utilizing the ISOWEEKNUM Function
The ISOWEEKNUM function gives the ISO week number of the year of a given date.
Syntax of the ISOWEEKNUM function:
The ISO week numbering system considers the week containing the first Thursday of the year as the first week of the year. Each week starts on Monday. ISO week numbers can vary from 1 to 53.
5 – Calculate Date Difference
5.1 Using the DATEDIF Function
The DATEDIF function calculates the difference between two dates in terms of years, months, or days.
Syntax of the DATEDIF function:
Units in this function are:
| Unit | Difference Type |
|---|---|
| D | Days between two dates. |
| M | Months between two dates. |
| Y | Years between two dates. |
| MD | Days between two dates ignoring months and years. |
| YM | Months between two dates ignoring years. |
| YD | Days between two dates ignoring years. |
5.2 Using the DAYS Function
The DAYS function gives the number of days between two dates.
Syntax of the DAYS function:
5.3 Applying the DAYS360 Function
The DAYS360 function calculates the difference between two dates using a 360-day year, assuming each month has 30 days.
Syntax of the DAYS360 function:
Method is an optional argument that specifies the day-count. TRUE is the European method and FALSE is the US method. Default is FALSE.
5.4 Applying the EDATE Function
The EDATE function adds or subtracts a specified number of months from a given date and returns the resulting date.
Syntax of the EDATE function:
5.5 Utilizing the YEARFRAC Function
The YEARFRAC function calculates the fraction of a year between two dates.
Syntax of the YEARFRAC function:
6 – Calculate Workdays
6.1 Using the WORKDAY Function
The WORKDAY function calculates a future or past date by skipping weekends and specified holidays.
Syntax of the WORKDAY function:
If there is a holiday in your time zone, you need to specify it in the holidays argument.
6.2 Applying the WORKDAY.INTL Function
The WORKDAY.INTL function calculates a future or past date by excluding specific weekends and holidays.
Syntax of the WORKDAY.INTL function:
 The WORKDAY.INTL function allows users to choose which days are considered as weekend.
6.3 Using the NETWORKDAYS Function
The NETWORKDAYS function calculates the number of working days excluding weekends and specified holidays between two dates.
Syntax of the NETWORKDAYS function:
6.4 Using the NETWORKDAYS.INTL Function
The NETWORKDAYS.INTL function calculates the number of working days excluding weekends and specified holidays between two dates using a custom weekend parameter.
Syntax of the NETWORKDAYS.INTL function:
The NETWORKDAYS.INTL function allows users to choose which days are considered as weekend.
Excel Time Functions
1 – HOUR, MINUTE & SECOND Functions
The HOUR, MINUTE, and SECOND functions are used to extract the hour, minute, and second from a given time.
Syntax of the HOUR function:
Syntax of the MINUTE function:
Syntax of the SECOND function:
2 – TIME Function
The TIME function creates a time value based on the provided hour, minute, and second.
Syntax of the TIME function:
3. TIMEVALUE Function
The TIMEVALUE function converts a time string (formatted as text) into a time value, a numeric representation of time.
Syntax of the TIMEVALUE function:
Frequently Asked Questions
1. How do I calculate someone’s age based on their birthdate?
Ans: Use the DATEDIF function:
=DATEDIF(Birthdate, TODAY(), "y")Replace Birthdate with the cell reference containing the birth date. The formula will return the person’s current age in years.
2. How do I convert a date to a different date format?
Ans: To convert a date to a different date format:
- Select the cell with the date.
- Go to the Home tab, and click Number Format.
- Choose More Number Formats and select a date format or create a custom format.
3. How do I work with dates and time in different time zones?
Ans: Ensure your dates/times are in a standardized format (e.g., UTC). Use the CONVERT function to adjust the time zone offset:
=CONVERT(Date_Time, "UTC", "Time_Zone")Replace Date_Time with the cell reference containing the date and time, and Time_Zone with the time zone code (e.g., -07:00 for Pacific Time).
<< Go Back to Excel Function Categories | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
