Tag

DATE / TIME FUNCTIONS

Browsing

The Excel TIMEVALUE function in Excel converts time into a decimal value representing the time as a proportion of the day in hours. In simple this function inputs time and returns what fraction of the day it is in a decimal format.

Purpose of Excel TIMEVALUE Function

To ascertain Decimal Value from Time which is a text string format. 

Return value

TIMEVALUE function returns a decimal number representing the entered time.

Syntax

=TIMEVALUE(time_text)

Arguments

ArgumentsDescriptionRemarks
time_textTime and/or Date in a text format recognized by Excel. Mandatory

Usage notes

  • Time_textshould be supplied in a text format that Excel can recognize, for example, “9:01 PM” or “19:10”.Time should be within quotation marks.
  • Date information in time_text is ignored.
  • TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel YEARFRAC function calculates the fraction of Year based on a number of whole days between the two entered dates(the start_date and the end_date), in a decimal format. In simple terms, it calculates the duration in years between two dates and returns a proportion when compared to the year(s).

Purpose of Excel YEARFRAC Function

To ascertain the proportion of the year based on two dates.

Return value

YEARFRAC returns a decimal value based on a proportion of the given dates compared to a year.

Syntax

=YEARFRAC(start_date, end_date, [basis])

Arguments

ArgumentsDescriptionRemarks
start_dateA start date.Mandatory
end_dateA end date.Mandatory
[basis]The type of day count. Optional

Usage notes

  • Only the date portion (not time) of start_date and end_date is used in the calculation.
  • Only the integer portion of the basis is used in the calculation. If a decimal value is provided, it will be Only the integer portion of basis is used in the calculation. If a decimal value is provided, it will be.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The YEAR function returns the year component of a date as a 4-digit number.

Purpose of Excel YEAR Function

You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.

Return value

YEAR as 4-digit number.

Syntax

=YEAR (date)

Arguments

ArgumentsDescriptionRemarks
dateA valid Excel dateMandatory

Usage notes

  • This function extracts the year from a given date as a 4-digit number.
  • Dates are serial numbers in Excel, and begin on January 1, 1900.
  • Dates before 1900 are not supported.
  • To display date values in a human-readable date format, apply a the number format of your choice. A valid Excel date.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The WORKDAY.INTL function takes a date and returns the nearest working in the future or past, based on an offset value you provide. Unlike the WORKDAY function, WORKDAY.INTL allows you to customize which days are considered weekends (non-working days).

Purpose of Excel WORKDAY.INTL Function

Excel WORKDAY.INTL function can be used when you want to get the date after a given number of working days.This function is best used when you want to calculate the invoice due date, project due date, delivery date, etc.

Return value

It returns the serial number of the date that is after or before the specified number of working days (from the specified start date).

Syntax

=WORKDAY.INTL (start_date, days, [weekend], [holidays])

Arguments

ArgumentsDescriptionRemarks
start_dateThe start dateMandatory
daysThe end dateMandatory
weekendSetting for which days of the week should be considered weekends Optional
holidaysA list of one or more dates that should be considered non-work days Optional

Usage notes

  • A Date can be entered as:
  • A result from some other function.
  • A date stored as text.
  • A date entered as text (In double quotes).
  • A Weekend could be any two consecutive days or any single day of the week.
  • In case of part-time jobs or non-consecutive non-working days, type the below format in [weekend] section.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The WORKDAY function takes a date and returns the nearest working day in the future or past, based on an offset value you provide.

Purpose of Excel WORKDAY Function

WORKDAY function can be used when you want to get the date after a given number of working days.This function is best suited when you want to calculate the invoice due date, project due date, delivery date, etc.

Return value

It returns the serial number of the date that is after or before the specified number of working days (from the specified start date).

Syntax

=WORKDAY (start_date, days, [holidays])

Arguments

ArgumentsDescriptionRemarks
start_dateThe date from which to startMandatory
daysThe working days before or after start_dateMandatory
holidaysA list dates that should be considered non-work days Optional

Usage notes

  • Saturday and Sunday are considered as the weekend and are not counted.
  • In case the weekends are days other than Saturday and Sunday, use WORKDAYS.INTL function.
  • A date can be entered as:
  • A result from some other function.
  • A date stored as text.
  • A date entered as text (in double quotes).

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday.

Purpose of Excel WEEKNUM Function

Get the week number for a given date.

Return value

A number between 1 and 54.

Syntax

=WEEKNUM (serial_num, [return_type])

Arguments

ArgumentsDescriptionRemarks
serial_numA valid Excel date in serial number formatMandatory
return_typeThe day the week begins. Default is 1Optional

Usage notes

  • Excel stores dates as sequential serial numbers so they can be used in calculations.
  • By default, January 1, 1900 is serial number 1.
  • January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • If Serial_number is out of range for the current date base value, a #NUM! error is returned.
  • If Return_type is out of the range specified in the table above, a #NUM! error is returned.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. You can use the WEEKDAY function inside other formulas to check the day of week and react as needed.

Purpose of Excel WEEKDAY Function

Get the day of the week as a number.

Return value

A number between 0 and 7.

Syntax

=WEEKDAY (serial_number, [return_type])

Arguments

ArgumentsDescriptionRemarks
serial_numberThe date for which you want to get the day of weekMandatory
return_typeA number representing day of week mapping scheme. Default is 1.Optional

Usage notes

  • This function will return a value even when the date is empty.
  • Take care to trap this result if blank dates are possible.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The TODAY function returns the current date, updated continuously when a worksheet is changed or opened. This function takes no arguments. You can format the value returned by using this function any standard date format. If you need current date and time, use the NOW function.

Purpose of Excel TODAY Function

Get the current date.

Return value

Valid Excel date.

Syntax

=TODAY ()

Arguments

ArgumentsDescriptionRemarks
No input arguments in today function

Usage notes

  • It recalculates whenever the excel workbook is open or whenever a calculation is triggered in the worksheet.
  • This means that the value of TODAY keeps on changing based on the current date.
  • Since volatile functions re-calculates so often, it adds to the processing time and slows down your workbook.
  • It can be forced to recalculate by pressing the F9 key. This recalculates the entire workbook calculations.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.

Purpose of Excel TIME Function

Create a time with hours, minutes, and seconds.

Return value

A decimal number representing a particular time in Excel.

Syntax

=TIME (hour, minute, second)

Arguments

ArgumentsDescriptionRemarks
hourThe hour for the time you wish to createMandatory
minuteThe minute for the time you wish to createMandatory
secondThe second for the time you wish to createMandatory

Usage notes

  • The TIME function creates a date in serial number format from the hour, minute, and second components you specify.
  • Use it to create a valid time when you have (or can supply) these component values separately.
  • Once you have a valid time, you can format it any way you like.Could be used to calculate benefits accrued to employees over time.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The SECOND function returns the second component of time as a number between 0-59.

Purpose of Excel SECOND Function

Get the SECOND as a number (0-59) from a Time.

Return value

A number between 0 and 59.

Syntax

=SECOND (serial_number)

Arguments

ArgumentsDescriptionRemarks
Serial NumberA valid time in a format Excel recognizesMandatory

Usage notes

  • Times can be supplied as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM).

Availability

Since 2007 for Windows.
Since 2011 for Mac.