Home / Formula Types / Date

What is a DATE formula in a spreadsheet?

Date formulas and functions allow you to manipulate and format dates and times. They enable you to perform calculations with dates, extract specific date components, determine time differences, and format dates according to various standards or custom requirements.

DATE formula usage examples.

The DATE function in Excel is used to convert a provided year, month, and day into a date. It returns a serial number representing the date.

The DATEDIF function calculates the number of days, months, or years between two dates. It is useful for calculating durations, ages, or tenures. The function takes three arguments: start_date, end_date, and unit. The start_date and end_date are the two dates between which the difference is calculated. The unit specifies the unit of measurement for the difference, which can be 'D' for days, 'M' for months, or 'Y' for years.

DATEVALUE

The DATEVALUE function converts a provided date string in a known format to a date value. It is useful when working with dates that are stored as text and need to be converted to a date format for further calculations or analysis.

The DAY function returns the day of the month that a specific date falls on, in numeric format. It extracts the day from a date and returns it as a number between 1 and 31.

The DAYS function calculates the number of days between two dates. It subtracts the start date from the end date and returns the result.

The DAYS360 function returns the difference between two days based on the 360-day year used in some financial interest calculations. It counts the number of days between two dates based on a 360-day year, where each month has 30 days.

The EDATE function returns a date that is a specified number of months before or after another date. It takes two arguments: start_date, which is the initial date, and months, which is the number of months to add or subtract. The function considers the number of days in each month and adjusts the result accordingly.

The EOMONTH function returns a date representing the last day of a month which falls a specified number of months before or after another date. It takes two arguments: start_date, which is the initial date, and months, which is the number of months to add or subtract from the start_date. The function calculates the last day of the month based on the start_date and the specified number of months.

EPOCHTODATE

The EPOCHTODATE function is used to convert a Unix epoch timestamp to a datetime. It takes two arguments: the timestamp and the unit of the timestamp (seconds, milliseconds, or microseconds). The function returns the corresponding datetime in UTC.

The HOUR function in Excel returns the hour component of a specific time, in numeric format. It extracts the hour value from a time value, ranging from 0 to 23.

ISOWEEKNUM

The ISOWEEKNUM function returns the number of the ISO week of the year where the provided date falls. ISO week numbering follows the ISO 8601 standard, where each week starts on a Monday and the first week of the year is the week that contains the first Thursday of the year.

The MINUTE function is used to extract the minute component from a time value. It returns the minute as a number between 0 and 59. This function is useful for performing calculations and analysis based on the minute component of a time value.

The MONTH function returns the month of the year a specific date falls in, in numeric format. It takes a date as input and returns an integer between 1 and 12, representing the month of the year.

NETWORKDAYS.INT

The NETWORKDAYS.INTL function is used to calculate the number of net working days between two provided dates, excluding specified weekend days and holidays. It takes the start_date and end_date as required arguments, and optionally accepts the [weekend] and [holidays] arguments to exclude specific days from the calculation. This function is useful for various scenarios such as project management, attendance tracking, and scheduling.

NETWORKDAYS

The NETWORKDAYS function returns the number of net working days between two provided days. It excludes weekends (Saturday and Sunday) by default, but can also exclude specified holidays if the [holidays] parameter is provided. The function is useful for calculating durations, tracking attendance, and other scenarios where the number of working days is important.

The NOW function returns the current date and time as a date value. It does not require any arguments and always returns the current date and time when the worksheet is recalculated.

The SECOND function returns the second component of a specific time, in numeric format. It extracts the seconds from a time value and can be used to perform calculations or comparisons based on the seconds component of time.

The TIME function in Excel is used to convert a provided hour, minute, and second into a time. It takes three arguments: hour, minute, and second. The hour argument represents the hour component of the time, the minute argument represents the minute component, and the second argument represents the second component. The function returns a time value that can be used in calculations or displayed in a cell as a time format.

TIMEVALUE

The TIMEVALUE function in Excel is used to convert a time string into a decimal representation of the time. It takes a time string as input and returns a decimal value representing the fraction of a 24-hour day that the time represents. The time string can be in any valid time format recognized by Excel, such as '9:00 AM' or '17:30'.

The WEEKDAY function in Excel returns a number representing the day of the week of the date provided. The function takes two arguments: 'date' is the date for which you want to determine the day of the week, and 'type' is an optional argument that specifies the numbering system for the days of the week (1 for Sunday to 7 for Saturday, or 2 for Monday to 1 for Sunday, and so on). If the 'type' argument is omitted, the default numbering system (1 for Sunday to 7 for Saturday) is used.

The WEEKNUM function returns a number representing the week of the year where the provided date falls. The optional 'type' argument allows you to specify the system used to determine the first week of the year. By default, the 'type' argument is set to 1, which means the week containing January 1st is considered as the first week of the year.

WORKDAY.INTL

The WORKDAY.INTL function calculates the date after a specified number of workdays, excluding specified weekend days and holidays. It takes the start date as the first argument, the number of workdays as the second argument, and optional arguments for specifying weekend days and holidays. The function returns the calculated date.

The WORKDAY function calculates the end date after a specified number of working days. It excludes weekends and optionally specified holidays. The function takes three arguments: start_date, which is the initial date; num_days, which is the number of working days to add to the start_date; and [holidays], which is an optional range of dates representing holidays to exclude from the calculation.

The YEAR function returns the year specified by a given date. It extracts the year from the date and returns it as a four-digit number.

YEARFRAC

The YEARFRAC function returns the number of years, including fractional years, between two dates using a specified day count convention. It is commonly used in financial calculations to determine interest rates, loan durations, and age calculations. The day_count_convention parameter is optional and determines how the days are counted between the start and end dates.

We use cookies on our site.