My Blog

Author: 5q981

  • TODAY function

    This article describes the formula syntax and usage of the TODAY function in Microsoft Excel.

    Description

    Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number.

    The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person’s age as of this year’s birthday:

    = YEAR( TODAY())-1963

    This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person’s age.

    Note: If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.

    Syntax

    TODAY()

    The TODAY function syntax has no arguments.

    Note: Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    FormulaDescriptionResult
    =TODAY()Returns the current date.12/1/2011
    =TODAY()+5Returns the current date plus 5 days. For example, if the current date is 1/1/2012, this formula returns 1/6/2012.12/6/2011
    =DATEVALUE(“1/1/2030”)-TODAY()Returns the number of days between the current date and 1/1/2030. Note that cell A4 must be formatted as General or Number for the result to display correctly.1/31/1918
    =DAY(TODAY())Returns the current day of the month (1 – 31).1
    =MONTH(TODAY())Returns the current month of the year (1 – 12). For example, if the current month is May, this formula returns 5.12

  • TIMEVALUE function

    This article describes the formula syntax and usage of the TIMEVALUE function in Microsoft Excel.

    Description

    Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

    Syntax

    TIMEVALUE(time_text)

    The TIMEVALUE function syntax has the following arguments:

    • Time_text    Required. A text string that represents a time in any one of the Microsoft Excel time formats; for example, “6:45 PM” and “18:45” text strings within quotation marks that represent time.

    Remarks

    • Date information in time_text is ignored.
    • Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    FormulaDescriptionResult
    =TIMEVALUE(“2:24 AM”)Decimal part of a day, with only the time specified.0.10
    =TIMEVALUE(“22-Aug-2011 6:35 AM”)Decimal part of a day, with date and time specified.0.2743

  • TIME function

    This article describes the formula syntax and usage of the TIME function in Microsoft Excel.

    Description

    Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date.

    The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

    Syntax

    TIME(hour, minute, second)

    The TIME function syntax has the following arguments:

    • Hour    Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.
    • Minute    Required. A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.
    • Second    Required. A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM

    Remark

    Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    HourMinuteSecond
    1200
    164810
    FormulaDescriptionResult
    =TIME(A2,B2,C2)Decimal part of a day, for the time specified in row 2 (12 hours, 0, minutes, 0 seconds)0.5
    =TIME(A3,B3,C3)Decimal part of a day, for the time specified in row 3 (16 hours, 48 minutes, 10 seconds)0.7001157

  • SECOND function

    This article describes the formula syntax and usage of the SECOND function in Microsoft Excel.

    Description

    Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

    Syntax

    SECOND(serial_number)

    The SECOND function syntax has the following arguments:

    • Serial_number    Required. The time that contains the seconds you want to find. Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE(“6:45 PM”)).

    Remark

    Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    Data
    Time
    4:48:18 PM
    4:48 PM
    FormulaDescriptionResult
    =SECOND(A3)Seconds in the first time (18)18
    =SECOND(A4)Seconds in the second time (0)0

  • NOW function

    This article describes the formula syntax and usage of the NOW function in Microsoft Excel. Find links to more information about working with dates and times in the See Also section.

    Description

    Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.

    The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

    Note: If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. These settings can be changed in Control Panel for the Excel desktop application.

    Syntax

    NOW()

    The NOW function syntax has no arguments.

    Remarks

    • Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
    • Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number 0.5 represents the time 12:00 noon.
    • The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    FormulaDescriptionResult
    =NOW()Returns the current date and time.11/6/2011 19:03
    =NOW()-0.5Returns the date and time 12 hours ago (-0.5 days ago).11/6/2011 7:03
    =NOW()+7Returns the date and time 7 days in the future.11/13/2011 19:03
    =NOW()-2.25Returns the date and time 2 days and 6 hours ago (-2.25 days ago).11/4/2011 13:03

  • NETWORKDAYS.INTL function

    Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

    Syntax

    NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

    The NETWORKDAYS.INTL function syntax has the following arguments:

    • Start_date and end_date    Required. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
    • Weekend    Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.Weekend number values indicate the following weekend days:
    Weekend numberWeekend days
    1 or omittedSaturday, Sunday
    2Sunday, Monday
    3Monday, Tuesday
    4Tuesday, Wednesday
    5Wednesday, Thursday
    6Thursday, Friday
    7Friday, Saturday
    11Sunday only
    12Monday only
    13Tuesday only
    14Wednesday only
    15Thursday only
    16Friday only
    17Saturday only

    Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

    For example, 0000011 would result in a weekend that is Saturday and Sunday.

    • Holidays    Optional. An optional set of one or more dates that are to be excluded from the working day calendar. holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

    Remarks

    • If start_date is later than end_date, the return value will be negative, and the magnitude will be the number of whole workdays.
    • If start_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
    • If end_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
    • If a weekend string is of invalid length or contains invalid characters, NETWORKDAYS.INTL returns the #VALUE! error value.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    FormulaDescriptionResult
    =NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))Results in 22 future workdays. Subtracts 9 nonworking weekend days (5 Saturdays and 4 Sundays) from the 31 total days between the two dates. By default, Saturday and Sunday are considered non-working days.22
    =NETWORKDAYS.INTL(DATE(2006,2,28),DATE(2006,1,31))Results in -21, which is 21 workdays in the past.-21
    =NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{“2006/1/2″,”2006/1/16”})Results in 22 future workdays by sutracting 10 nonworking days (4 Fridays, 4 Saturdays, 2 Holidays) from the 32 days between Jan 1 2006 and Feb 1 2006. Uses the 7 argument for weekend, which is Friday and Saturday. There are also two holidays in this time period.22
    =NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),”0010001″,{“2006/1/2″,”2006/1/16”})Results in 22 future workdays. Same time period as example directly above, but with Sunday and Wednesday as weekend days.20

  • NETWORKDAYS function

    This article describes the formula syntax and usage of the NETWORKDAYS function in Microsoft Excel.

    Description

    Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

    Tip:  To calculate whole workdays between two dates by using parameters to indicate which and how many days are weekend days, use the NETWORKDAYS.INTL function.

    Syntax

    NETWORKDAYS(start_date, end_date, [holidays])

    The NETWORKDAYS function syntax has the following arguments:

    • Start_date    Required. A date that represents the start date.
    • End_date    Required. A date that represents the end date.
    • Holidays    Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

    Important:  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.

    Remarks

    • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2012 is serial number 40909 because it is 40,909 days after January 1, 1900.
    • If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    DateDescription
    10/1/2012Start date of project
    3/1/2013End date of project
    11/22/2012Holiday
    12/4/2012Holiday
    1/21/2013Holiday
    FormulaDescriptionResult
    =NETWORKDAYS(A2,A3)Number of workdays between the start (10/1/2012) and end date (3/1/2013).110
    =NETWORKDAYS(A2,A3,A4)Number of workdays between the start (10/1/2012) and end date (3/1/2013), with the 11/22/2012 holiday as a non-working day.109
    =NETWORKDAYS(A2,A3,A4:A6)Number of workdays between the start (10/1/2012) and end date (3/1/2013), with the three holidays as non-working days.107

  • MONTH function

    This article describes the formula syntax and usage of the MONTH function in Microsoft Excel.

    Description

    Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

    Syntax

    MONTH(serial_number)

    The MONTH function syntax has the following arguments:

    • Serial_number    Required. The date of the month you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

    Remarks

    Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

    Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    Date
    15-Apr-11
    FormulaDescriptionResult
    =MONTH(A2)Month of the date in cell A24

  • MINUTE function

    This article describes the formula syntax and usage of the MINUTE function in Microsoft Excel.

    Description

    Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

    Syntax

    MINUTE(serial_number)

    The MINUTE function syntax has the following arguments:

    • Serial_number    Required. The time that contains the minute you want to find. Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE(“6:45 PM”)).

    Remarks

    Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5, since it is half of a day).

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    Time
    12:45:00 PM
    FormulaDescriptionResult
    =MINUTE(A2)Minute portion of the time in A2.45

  • ISOWEEKNUM function

    This article describes the formula syntax and usage of the ISOWEEKNUM function in Microsoft Excel.

    Description

    Returns number of the ISO week number of the year for a given date.

    Syntax

    ISOWEEKNUM(date)

    The ISOWEEKNUM function syntax has the following arguments.

    • Date    Required. Date is the date-time code used by Excel for date and time calculation.

    Remarks

    • Microsoft Excel stores dates as sequential numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
    • If the date argument is not a valid number, ISOWEEKNUM returns the #NUM! error value.
    • If the date argument is not a valid date type, ISOWEEKNUM returns the #VALUE! error value.

    Example

    Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

    Date
    3/9/2012
    FormulaDescriptionResult
    =ISOWEEKNUM(A2)Number of the week in the year that 3/9/2012 occurs, based on weeks beginning on the default, Monday (10).10