Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas
127 Formulas
11 Categories
3 Skill levels
Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Math & Arithmetic Statistical Analysis Logical & Conditional Lookup & Reference Date & Time Text & Strings Information & Arrays Dynamic Arrays (365) Enhanced Lookup (365) Advanced Calculation (365) Text & Reshaping (365)

Chapter 5

Date & Time

  1. 47 NOW =NOW() Returns the serial number of the current date and time. Recalculates whenever the sheet is evaluated. Date & Time Beginner Classic

    Syntax

    =NOW()

    Arguments

    No arguments
    Takes no arguments. Parentheses are required.

    Sample

    =NOW()

    Result 46276.6041666667

    Sample data

    Download
    ABCD
    2Order Created11-Sep-2026 14:30LoggedVolatile
    3Report Run11-Sep-2026 14:30PrintedVolatile
    4Daily Sync11-Sep-2026 14:30CompletedVolatile
    Pro tip

    To insert a static non-changing timestamp that does not update, press Ctrl + Shift + ; for time or Ctrl + ; for date.

    Common pitfall

    NOW is a volatile function that recalculates every time the sheet recalculates.

  2. 48 TIME =TIME(hour, minute, second) Converts hours, minutes, and seconds given as numbers to an Excel serial time fraction. Excel stores times as fractions of a 24-hour day. Date & Time Beginner Classic

    Syntax

    =TIME(hour, minute, second)

    Arguments

    hour
    A number from 0 to 32767 representing the hour.
    minute
    A number from 0 to 32767 representing the minute.
    second
    A number from 0 to 32767 representing the second.

    Sample

    =TIME(1, 1, 1)

    Result 0.0423726851851852

    Sample data

    Download
    ABCD
    21111:01:01 AM
    31430002:30:00 PM
    4945159:45:15 AM
    Pro tip

    Values greater than 23 for hours roll over into days (e.g. TIME(25, 0, 0) is 1:00 AM the next day).

    Common pitfall

    Make sure your cell is formatted as Time (Format Cells -> Time) so it displays as clock time rather than a decimal.

  3. 49 HOUR =HOUR(serial_number) Returns the hour component of a time value as an integer from 0 to 23. Date & Time Beginner Classic

    Syntax

    =HOUR(serial_number)

    Arguments

    serial_number
    The time value containing the hour you want to find.

    Sample

    =HOUR(A2)

    Result 14

    Sample data

    Download
    ABCD
    214:35:2014Afternoon Shift2 PM
    309:12:459Morning Shift9 AM
    420:45:0020Evening Shift8 PM
    Pro tip

    Excel uses 24-hour military time internally for HOUR, so 2:00 PM returns 14.

    Common pitfall

    Passing text strings that do not recognize standard time formats throws #VALUE!.

  4. 50 MINUTE =MINUTE(serial_number) Returns the minute component of a time value as an integer from 0 to 59. Date & Time Beginner Classic

    Syntax

    =MINUTE(serial_number)

    Arguments

    serial_number
    The time value that contains the minute you want to find.

    Sample

    =MINUTE(A2)

    Result 29

    Sample data

    Download
    ABCD
    210:29:152920-30mOn Time
    309:05:0050-10mEarly
    411:45:304540-50mLate
    Pro tip

    To calculate total elapsed minutes from midnight: =HOUR(A2)*60 + MINUTE(A2).

    Common pitfall

    Time values must be valid Excel times or serial numbers; plain unstructured text will fail.

  5. 51 SECOND =SECOND(serial_number) Returns the second component of a time value as an integer ranging from 0 to 59. Date & Time Beginner Classic

    Syntax

    =SECOND(serial_number)

    Arguments

    serial_number
    The time value containing the second you want to extract.

    Sample

    =SECOND(A2)

    Result 45

    Sample data

    Download
    ABCD
    212:30:45450.521354Exact second pulled
    308:15:0000.343750Zero second mark
    419:40:18180.819653Standard event
    Pro tip

    Combine HOUR, MINUTE, and SECOND to calculate total seconds elapsed: =HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2).

    Common pitfall

    Seconds precision beyond integers (milliseconds) is truncated by SECOND().

  6. 52 DAY =DAY(serial_number) Returns the day of the month corresponding to a date as an integer from 1 to 31. Date & Time Beginner Classic

    Syntax

    =DAY(serial_number)

    Arguments

    serial_number
    The date of the day you are trying to find.

    Sample

    =DAY(A2)

    Result 10

    Sample data

    Download
    ABCD
    210-Apr-202510Mid-MonthCycle 1
    301-Jan-20251Month StartCycle 1
    428-Feb-202528Month EndCycle 2
    Pro tip

    Use DAY(EOMONTH(A2, 0)) to calculate the total number of days in the current month (28, 29, 30, or 31).

    Common pitfall

    If date is entered as text in an unrecognized locale format (e.g. DD/MM vs MM/DD), DAY may swap month and day.

  7. 53 MONTH =MONTH(serial_number) Returns the month of a date represented by a serial number. The month is given as an integer from 1 to 12. Date & Time Beginner Classic

    Syntax

    =MONTH(serial_number)

    Arguments

    serial_number
    The date of the month you are trying to find.

    Sample

    =MONTH(A2)

    Result 4

    Sample data

    Download
    ABCD
    210-Apr-20254AprilQ2
    301-Jan-20251JanuaryQ1
    415-Aug-20258AugustQ3
    Pro tip

    To get the 3-letter month name instead of a number, use =TEXT(A2, "mmm") ("Apr") or "mmmm" ("April").

    Common pitfall

    Excel stores empty cells as date 0 (0-Jan-1900), so MONTH on an empty cell returns 1.

  8. 54 YEAR =YEAR(serial_number) Date & Time Beginner Classic

    Syntax

    =YEAR(serial_number)

    Arguments

    serial_number
    The date of the year you want to find.

    Sample

    =YEAR(A2)

    Result 2025

    Sample data

    Download
    ABCD
    210-Apr-20252025FY2025Current
    315-Nov-20242024FY2024Archived
    405-Jun-20232023FY2023Audited
    Pro tip

    Always use 4-digit years in Excel formulas to avoid century ambiguity between 1925 and 2025.

    Common pitfall

    An empty cell passed to YEAR returns 1900.

  9. 55 DATE =DATE(year, month, day) Creates a valid Excel date serial number given separate year, month, and day integers. Safe from regional formatting confusion. Date & Time Beginner Classic

    Syntax

    =DATE(year, month, day)

    Arguments

    year
    Four-digit integer representing the year.
    month
    Integer representing month (1 to 12).
    day
    Integer representing day of month (1 to 31).

    Sample

    =DATE(2025, 1, 1)

    Result 45658

    Sample data

    Download
    ABCD
    220251101-Jan-2025
    3202541010-Apr-2025
    42025122525-Dec-2025
    Pro tip

    DATE handles month overflow automatically: =DATE(2025, 13, 1) cleanly returns 01-Jan-2026!

    Common pitfall

    Two-digit years (like 25) are interpreted according to Excel century rules (1925 vs 2025); always supply 4 digits.

  10. 56 DATEVALUE =DATEVALUE(date_text) Converts a date stored as text to a serial number that Excel recognizes as a date. Enables date math on imported text data. Date & Time Intermediate Classic

    Syntax

    =DATEVALUE(date_text)

    Arguments

    date_text
    Text that represents a date in an Excel date format.

    Sample

    =DATEVALUE("01-Jan-2025")

    Result 45658 (01-Jan-2025)

    Sample data

    Download
    ABCD
    2"01-Jan-2025"4565801-Jan-2025Date Serial
    3"10/04/2025"4575710-Apr-2025Date Serial
    4"2025-12-31"4602231-Dec-2025Date Serial
    Pro tip

    After using DATEVALUE, format the target cell with Short Date (Ctrl + Shift + #) to view in standard calendar format.

    Common pitfall

    If date_text includes time information, DATEVALUE ignores the time portion.

  11. 57 WEEKDAY =WEEKDAY(serial_number, [return_type]) Returns the day of the week for a specified date. Default return_type gives 1 for Sunday through 7 for Saturday. Date & Time Intermediate Classic

    Syntax

    =WEEKDAY(serial_number, [return_type])

    Arguments

    serial_number
    A sequential number representing the date to evaluate.
    [return_type]
    1: Sun(1)-Sat(7), 2: Mon(1)-Sun(7), 3: Mon(0)-Sun(6).

    Sample

    =WEEKDAY(A2, 1)

    Result 5 (Thursday)

    Sample data

    Download
    ABCD
    210-Apr-20255ThursdayNo
    312-Apr-20257SaturdayYes (Weekend)
    413-Apr-20251SundayYes (Weekend)
    Pro tip

    Use return_type = 2 so Monday is 1 and Sunday is 7: then the weekend test is simply =WEEKDAY(A2, 2) >= 6.

    Common pitfall

    Default return_type is 1 (Sunday = 1), which can cause off-by-one errors for Monday-start work environments.

  12. 58 WEEKNUM =WEEKNUM(serial_number, [return_type]) Returns the week number of a specific date. The week containing January 1 is designated as week 1 of that year. Date & Time Intermediate Classic

    Syntax

    =WEEKNUM(serial_number, [return_type])

    Arguments

    serial_number
    The date within the week you want to find.
    [return_type]
    1: Week begins on Sunday, 2: Week begins on Monday.

    Sample

    =WEEKNUM(A2, 1)

    Result 15

    Sample data

    Download
    ABCD
    210-Apr-202515Sprint 15Q2
    301-Jan-20251Sprint 1Q1
    415-Aug-202533Sprint 33Q3
    Pro tip

    For international ISO 8601 week numbering compliance (European standard), use ISOWEEKNUM(date) or return_type 21.

    Common pitfall

    Different return_type values change which week is considered Week 1 if Jan 1 falls mid-week.

  13. 59 NETWORKDAYS =NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any specified holidays. Date & Time Intermediate Classic

    Syntax

    =NETWORKDAYS(start_date, end_date, [holidays])

    Arguments

    start_date
    Start date of the time span.
    end_date
    End date of the time span.
    [holidays]
    Optional range of one or more dates to exclude from the working calendar.

    Sample

    =NETWORKDAYS(A2, B2, C2:C3)

    Result 21

    Sample data

    Download
    Pro tip

    NETWORKDAYS is inclusive of both start_date and end_date if they are normal working days.

    Common pitfall

    Assumes Saturday and Sunday are the weekend. For Middle Eastern countries (Fri/Sat), use NETWORKDAYS.INTL.

  14. 60 NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) Returns the number of whole workdays between two dates with custom weekend parameters. Date & Time Intermediate Classic

    Syntax

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

    Arguments

    start_date, end_date
    Dates between which the workdays are calculated.
    [weekend]
    Weekend code (e.g. 1=Sat/Sun, 11=Sunday only, 7=Fri/Sat) or a 7-character string.
    [holidays]
    Range of dates to exclude as holidays.

    Sample

    =NETWORKDAYS.INTL(A2, B2, 11, C2)

    Result 25

    Sample data

    Download
    ABCD
    201-Apr-202530-Apr-202511 (Sunday only)25 Days (6-day week)
    301-Apr-202530-Apr-20251 (Sat & Sun)22 Days (5-day week)
    401-Apr-202530-Apr-20257 (Fri & Sat)22 Days (Gulf week)
    Pro tip

    Use a 7-character binary mask for custom shifts (e.g. "0000011" represents Monday-Friday work, Sat-Sun off).

    Common pitfall

    In binary string masks, "1" indicates a NON-working day (weekend) and "0" indicates a working day.

  15. 61 WORKDAY =WORKDAY(start_date, days, [holidays]) Returns a date that is a designated number of working days ahead or behind a start date, skipping weekends and holidays. Date & Time Intermediate Classic

    Syntax

    =WORKDAY(start_date, days, [holidays])

    Arguments

    start_date
    The initial date.
    days
    The number of non-weekend and non-holiday days to add (positive) or subtract (negative).
    [holidays]
    Optional range of holiday dates to skip.

    Sample

    =WORKDAY(A2, 10, C2:C3)

    Result 45672

    Sample data

    Download
    Pro tip

    Use WORKDAY.INTL if your organization works 6 days a week or has non-standard weekend days.

    Common pitfall

    Remember to format the output cell as a Date, otherwise you will see a raw 5-digit serial number like 45672.

  16. 62 EDATE =EDATE(start_date, months) Returns the serial number that represents the date that is the indicated number of months before or after a start date. Date & Time Intermediate Classic

    Syntax

    =EDATE(start_date, months)

    Arguments

    start_date
    A date representing the starting date.
    months
    Number of months before (negative) or after (positive) start_date.

    Sample

    =EDATE(A2, 1)

    Result 45787

    Sample data

    Download
    ABCD
    210-Apr-2025110-May-2025+1 Month Added
    310-Apr-2025610-Oct-2025+6 Months Added
    410-Apr-2025-110-Mar-2025-1 Month Subtracted
    Pro tip

    EDATE handles leap years and variable month lengths intelligently (Jan 31 + 1 month becomes Feb 28 or 29).

    Common pitfall

    Always format output cell as Date; unformatted cells display raw numerical serial numbers.

  17. 63 EOMONTH =EOMONTH(start_date, months) Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Date & Time Intermediate Classic

    Syntax

    =EOMONTH(start_date, months)

    Arguments

    start_date
    The starting date.
    months
    The number of months before or after start_date. 0 = current month.

    Sample

    =EOMONTH(A2, 0)

    Result 45777

    Sample data

    Download
    ABCD
    210-Apr-2025030-Apr-2025Current month end
    310-Jan-2025128-Feb-2025Next month end (Feb)
    415-Feb-2024029-Feb-2024Leap year handled
    Pro tip

    To find the first day of next month: =EOMONTH(A2, 0) + 1.

    Common pitfall

    Passing months as decimal (e.g. 1.5) will be truncated by Excel to an integer (1).