Chapter 27: All Date and Time Functions in Excel

Introduction to Date and Time Functions
Date and time functions in Excel allow you to perform a wide range of operations on dates and times, from simple calculations to complex manipulations. This chapter will cover essential date and time functions, explaining each one in detail and providing examples to help you understand their applications.

1. Date Functions

DATE

• Purpose: Creates a date value from year, month, and day.
• Syntax: =DATE(year, month, day)
• Example: =DATE(2024, 9, 8) returns 08-Sep-2024.

DATEDIF

• Purpose: Calculates the difference between two dates in years, months, or days.
• Syntax: =DATEDIF(start_date, end_date, “unit”)
• Units: “Y” for years, “M” for months, “D” for days.
• Example: =DATEDIF(“2020-01-01”, “2024-09-08”, “Y”) returns 4 (years).

DATEVALUE

• Purpose: Converts a date in text format to a date value.
• Syntax: =DATEVALUE(date_text)
• Example: =DATEVALUE(“September 8, 2024”) returns 08-Sep-2024.

DAY

• Purpose: Extracts the day of the month from a date.
• Syntax: =DAY(date)
• Example: =DAY(“2024-09-08”) returns 8.

DAYS

• Purpose: Calculates the number of days between two dates.
• Syntax: =DAYS(end_date, start_date)
• Example: =DAYS(“2024-09-08”, “2024-01-01”) returns 250.

DAYS360

• Purpose: Calculates the number of days between two dates based on a 360-day year.
• Syntax: =DAYS360(start_date, end_date, [method])
• Example: =DAYS360(“2024-01-01”, “2024-09-08”) returns 249.

EDATE

• Purpose: Adds or subtracts a specified number of months to a date.
• Syntax: =EDATE(start_date, months)
• Example: =EDATE(“2024-01-01”, 6) returns 01-Jul-2024.

EOMONTH

• Purpose: Returns the last day of the month, a specified number of months before or after a given date.
• Syntax: =EOMONTH(start_date, months)
• Example: =EOMONTH(“2024-01-01”, 3) returns 31-Mar-2024.

HOUR

• Purpose: Extracts the hour from a time value.
• Syntax: =HOUR(time)
• Example: =HOUR(“15:45:30”) returns 15.

ISO WEEKNUM

• Purpose: Returns the ISO week number of a date.
• Syntax: =ISOWEEKNUM(date)
• Example: =ISOWEEKNUM(“2024-09-08”) returns 36.

MINUTE

• Purpose: Extracts the minute from a time value.
• Syntax: =MINUTE(time)
• Example: =MINUTE(“15:45:30”) returns 45.

MONTH

• Purpose: Extracts the month from a date.
• Syntax: =MONTH(date)
• Example: =MONTH(“2024-09-08”) returns 9.

NETWORKDAYS

• Purpose: Calculates the number of working days between two dates.
• Syntax: =NETWORKDAYS(start_date, end_date, [weekend], [holidays])
• Example: =NETWORKDAYS(“2024-01-01”, “2024-09-08”) returns 186.

NETWORKDAYS.INTL

• Purpose: Calculates the number of working days between two dates with custom weekends.
• Syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
• Example: =NETWORKDAYS.INTL(“2024-01-01”, “2024-09-08”, 1) returns 186 (with default weekends).

NOW

• Purpose: Returns the current date and time.
• Syntax: =NOW()
• Example: =NOW() returns 08-Sep-2024 10:30 AM (current date and time).

SECOND

• Purpose: Extracts the second from a time value.
• Syntax: =SECOND(time)
• Example: =SECOND(“15:45:30”) returns 30.

TIME

• Purpose: Creates a time value from hours, minutes, and seconds.
• Syntax: =TIME(hour, minute, second)
• Example: =TIME(15, 45, 30) returns 15:45:30.

TIMEVALUE

• Purpose: Converts a time in text format to a time value.
• Syntax: =TIMEVALUE(time_text)
• Example: =TIMEVALUE(“15:45:30”) returns 15:45:30.

WEEKDAY

• Purpose: Returns the day of the week as a number (1 for Sunday through 7 for Saturday).
• Syntax: =WEEKDAY(date, [return_type])
• Example: =WEEKDAY(“2024-09-08”) returns 1 (Sunday).

WEEKNUM

• Purpose: Returns the week number of a date.
• Syntax: =WEEKNUM(date, [return_type])
• Example: =WEEKNUM(“2024-09-08”) returns 36.

WORKDAY

• Purpose: Returns a date that is a specified number of working days before or after a given date.
• Syntax: =WORKDAY(start_date, days, [weekend], [holidays])
• Example: =WORKDAY(“2024-01-01”, 10) returns 15-Jan-2024.

WORKDAY.INTL

• Purpose: Returns a date that is a specified number of working days before or after a given date with custom weekends.
• Syntax: =WORKDAY.INTL(start_date, days, [weekend], [holidays])
• Example: =WORKDAY.INTL(“2024-01-01”, 10, 1) returns 15-Jan-2024 (with default weekends).

YEAR

• Purpose: Extracts the year from a date.
• Syntax: =YEAR(date)
• Example: =YEAR(“2024-09-08”) returns 2024.

2. Practice Assignment

1. Date Calculations:
   • Calculate the number of days between two dates using DAYS.
   • Determine the last day of the month for a given date using EOMONTH.
2. Time Functions:
   • Extract hours, minutes, and seconds from a time value using HOUR, MINUTE, and SECOND.
   • Create a time value using TIME.
3. Network Days:
   • Calculate the number of working days between two dates using NETWORKDAYS.
4. Current Date and Time:
   • Display the current date and time using NOW.
5. Custom Functions:
   • Use WORKDAY and WORKDAY.INTL to find a date that is a certain number of working days before or after a given date.