Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • How to Create One and Two Variable Data Tables in Excel
  • How to create a Histogram in Excel
  • Subtotal function in Excel
  • How to calculate average last N values in a table in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel LOOKUP Function
  • How to get relative row numbers in a range in Excel
  • How to reference named range different sheet in Excel
  • How to get address of last cell in range in Excel

Data Validations

  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation no punctuation

Category: Date and Time Functions

Excel Date and Time Functions return a dynamic date or time in a cell.

Excel Date & Time Functions Example

by

To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the “:” (colon). You can also enter a date and a time in one cell. Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings. Learn more about Date and Time formats. Year, …

Continue Reading

NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation

by

What is NETWORKDAYS.INTL function in Excel? NETWORKDAYS.INTL function is one of Date and Time functions in Microsoft Excel that 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 …

Continue Reading

DATE function: Description, Usage, Syntax, Examples and Explanation

by

What is DATE function in Excel? DATE function is one of Date and Time functions in Microsoft Office Excel that when you need to take three separate values and combine them to form a date. Note: Microsoft Excel DATE function returns the serial date value for a date. Syntax of DATE function The syntax for the DATE function in …

Continue Reading

TODAY function: Description, Usage, Syntax, Examples and Explanation

by

What is TODAY function in Excel? TODAY function is one of  Date and Time functions in Microsoft Excel that 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 …

Continue Reading

TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation

by

What is TIMEVALUE function in Excel? TIMEVALUE function is one of  Date and Time functions in Microsoft Excel that 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 of TIMEVALUE …

Continue Reading

TIME function: Description, Usage, Syntax, Examples and Explanation

by

What is TIME function in Excel? TIME function is one of  Date and Time functions in Microsoft Excel that 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) …

Continue Reading

NOW function: Description, Usage, Syntax, Examples and Explanation

by

What is NOW function in Excel? NOW function is one of  Date and Time functions in Microsoft Excel that 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. …

Continue Reading

WEEKDAY function: Description, Usage, Syntax, Examples and Explanation

by

What is WEEKDAY function in Excel? WEEKDAY function is one of Date and Time functions in Microsoft Excel that returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. Syntax of WEEKDAY function WEEKDAY(serial_number,[return_type]) The WEEKDAY function syntax has the following arguments: …

Continue Reading

WEEKNUM function: Description, Usage, Syntax, Examples and Explanation

by

What is WEEKNUM function in Excel? WEEKNUM function is one of Date and Time functions in Microsoft Excel that returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1. There are two systems used for this function: System 1    The week …

Continue Reading

SECOND function: Description, Usage, Syntax, Examples and Explanation

by

What is SECOND function in Excel? SECOND function is one of Date and Time functions in Microsoft Excel that returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59. Syntax of SECOND function SECOND(serial_number) The SECOND function syntax has the following arguments: Serial_number    Required. The time that contains …

Continue Reading

WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation

by

What is WORKDAY.INTL function in Excel? WORKDAY.INTL function is one of Date and Time functions in Microsoft Excel that returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified …

Continue Reading

WORKDAY function: Description, Usage, Syntax, Examples and Explanation

by

What is WORKDAY function in Excel? WORKDAY function is one of Date and Time functions in Microsoft Excel that returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends …

Continue Reading

YEAR function: Description, Usage, Syntax, Examples and Explanation

by

What is YEAR function in Excel? YEAR function is one of Date and Time functions in Microsoft Excel that returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999. Syntax of YEAR function YEAR(serial_number) The YEAR function syntax has the following arguments: Serial_number: The date of the year you want …

Continue Reading

YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

by

What is DATEDIF function in Excel? DATEDIF function is one of Date and Time functions in Microsoft Excel that calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year’s benefits, or obligations to assign to a specific term. …

Continue Reading

DATEDIF function: Description, Usage, Syntax, Examples and Explanation

by

What is DATEDIF function in Excel? DATEDIF function is one of Date and Time functions in Microsoft Excel that calculates the number of days, months, or years between two dates. Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. Syntax of DATEDIF function DATEDIF(start_date,end_date,unit) …

Continue Reading

DATEVALUE function: Description, Usage, Syntax, Examples and Explanation

by

What is DATEVALUE function in Excel? DATEVALUE function is one of Date and Time functions in Microsoft Excel that converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE(“1/1/2008”) returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer’s system date setting may …

Continue Reading

DAY function: Description, Usage, Syntax, Examples and Explanation

by

What is DAY function in Excel? DAY function is one of Date and Time functions in Microsoft Excel that returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. Syntax of DAY function DAY(serial_number) The DAY function syntax has the following arguments: Serial_number: The date …

Continue Reading

DAYS360 function: Description, Usage, Syntax, Examples and Explanation

by

What is DAYS360 function in Excel? DAYS360 function is one of Date and Time functions in Microsoft Excel that returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve …

Continue Reading

EDATE function: Description, Usage, Syntax, Examples and Explanation

by

What is EDATE function in Excel? EDATE function is one of Date and Time functions in Microsoft Excel that returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of …

Continue Reading

EOMONTH function: Description, Usage, Syntax, Examples and Explanation

by

What is EOMONTH function in Excel? EOMONTH function is one of Date and Time functions in Microsoft Excel that returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of …

Continue Reading

HOUR function: Description, Usage, Syntax, Examples and Explanation

by

What is HOUR function in Excel? HOUR function is one of Date and Time functions in Microsoft Excel that returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Syntax of HOUR function HOUR(serial_number) The HOUR function syntax has the following arguments: Serial_number: The time …

Continue Reading

ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation

by

What is ISOWEEKNUM function in Excel? ISOWEEKNUM function is one of Date and Time functions in Microsoft Excel that returns number of the ISO week number of the year for a given date. Syntax of ISOWEEKNUM function ISOWEEKNUM(date) The ISOWEEKNUM function syntax has the following arguments. Date: Date is the date-time code used by Excel for date and …

Continue Reading

MINUTE function: Description, Usage, Syntax, Examples and Explanation

by

What is MINUTE function in Excel? MINUTE function is one of Date and Time functions in Microsoft Excel that returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. Syntax of MINUTE function MINUTE(serial_number) The MINUTE function syntax has the following arguments: Serial_number: The time that contains the minute …

Continue Reading

MONTH function: Description, Usage, Syntax, Examples and Explanation

by

What is MONTH function in Excel? MONTH function is one of Date and Time functions in Microsoft Excel that 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 of MONTH function MONTH(serial_number) The MONTH function syntax has the following arguments: Serial_number    Required. …

Continue Reading

NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation

by

What is NETWORKDAYS function in Excel? NETWORKDAYS function is one of Date and Time functions in Microsoft Excel that 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 …

Continue Reading

DAYS function: Description, Usage, Syntax, Examples and Explanation

by

What is DAYS function in Excel? DAYS function is one of Date and Time functions in Microsoft Excel that returns the number of days between two dates. Syntax of DAYS function DAYS(end_date, start_date) The DAYS function syntax has the following arguments. End_date    Required. Start_date and End_date are the two dates between which you want to know the number …

Continue Reading

How to calculate next day of week in Excel

by

To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. Formula =date+7-WEEKDAY(date+7-dow) Explanation  In the example shown, the formula in D6 is: =B6+7-WEEKDAY(B6+7-2) Where B6 contains the date Friday, January 16, 2015, and 2 represents Monday, the “dow”. …

Continue Reading

How to calculate next scheduled event in Excel

by

To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH  to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …

Continue Reading

How to calculate most recent day of week in Excel

by

If you want to find the most recent Wednesday, or Saturday, or Monday, given a specific start date, you can use a formula that uses the MOD function. Formula =date-MOD(date-dow,7) Note: If the the date has the same day of week, date will be returned. Explanation   Formulas that use the concept of “day of …

Continue Reading

How to calculate months between dates in Excel

by

To calculate months between two dates as a whole number, you can use the DATEDIF function. Formula =DATEDIF(start_date,end_date,”m”) Explanation In the example shown, the formula in D6 is: =DATEDIF(B6,C6,”m”) Note that the DATEDIF automatically rounds down. To round up to the nearest month, see below. The mystery of DATEDIF The DATEDIF function is a “compatibility” …

Continue Reading

How to calculate nth day of week in month in Excel

by

To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions. Formula =date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow) Note:  Day of week is abbreviated “dow” in the generic form of the formula. This is a number between 1 (Sunday) and 7 …

Continue Reading

How to calculate percent of year complete in Excel

by

If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function. Formula =YEARFRAC(DATE(YEAR(date),1,1),date) Explanation In the example, the formula in C5, which calculates percentage of year remaining, is: =YEARFRAC(DATE(YEAR(B5),1,1),B5) How the formula works the YEARFRAC function takes two dates, …

Continue Reading

How to calculate nth day of year in Excel

by

To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. Formula =date-DATE(YEAR(date),1,0) Explanation In the example shown, the formula in C5 is: =B4-DATE(YEAR(B4),1,0) With the date “June 1, 2016” in cell B4, the formula returns 153, since June 1st is the 153rd day …

Continue Reading

How to calculate project start date based on end date in Excel

by

To calculate a project start date or custom date based on an end date and duration in days, you can use the WORKDAY function. Note: WORKDAY function does not count the start date as a workday. Formula =WORKDAY(start,-days,holidays) Explanation In the example shown, the formula in E5 is: =WORKDAY(C5,-D5,holidays) where “holidays” is the named range G5:G9. How …

Continue Reading

How to calculate quarter from date in Excel

by

If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH. Formula =ROUNDUP(MONTH(date)/3,0) Explanation In the example shown, the formula in cell C5 is: =ROUNDUP(MONTH(B5)/3,0) How this formula works In this case, the formula first extracts the month as a number between 1-12, then divides …

Continue Reading

How to get same date next month or previous month in Excel

by

To get the same date next month from a given date, you can use the EDATE function. Formula =EDATE(date,1) Explanation In the example shown, the formula in cell B5 is: =EDATE(B5,1) How this formula works EDATE can get the “same date” in the future or past, based on the number of months supplied. When 1 …

Continue Reading

How to get same date next year or previous year in Excel

by

To get the same date next year from a given date, you can use the EDATE function. Formula =EDATE(date,12) Explanation In the example shown, the formula in cell B5 is: =EDATE(B5,12) EDATE can get the “same date” in the future or past, based on the number of months supplied. When 12 is given for months, …

Continue Reading

Get project midpoint in Excel

by

To calculate an approximate project midpoint date, you can use the WORKDAY function. Note: The WORKDAY function returns a date in the future or past, based on a start date, workdays, and optional holidays. WORKDAY automatically excludes weekends, and counts only Monday through Friday as workdays. Formula =WORKDAY(start,days/2,holidays) Explanation In the example shown, the formula in E5 …

Continue Reading

Get project end date in Excel

by

To calculate a project end date based on a start date and duration, you can use the WORKDAY function. Formula =WORKDAY(start,days,holidays) Explanation In the example shown, the formula in E5 is: =WORKDAY(C5,D5,holidays) where “holidays” is the named range G5:G9. How this formula works This formula uses the WORKDAY function to calculate an end date. WORKDAY can calculate dates …

Continue Reading

Get week number from date in Excel

by

If you need to get the week number from the day from a date, you can use the WEEKNUM function. The date must be in a form that Excel recognizes as a valid date. Note: The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate …

Continue Reading

Posts navigation

  • 1
  • 2
  • 3
  • 4
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF with boolean logic in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • Check multiple cells are equal in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel

Date Time

  • Add days exclude certain days of week in Excel
  • Get day from date in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • How to calculate future date say 6 months ahead in Excel
  • Find Last Day of the Month in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into unequal buckets in Excel
  • Categorize text with keywords in Excel

General

  • Find Most Frequently Occurring Word in Excel Worksheet
  • Subtotal by invoice number in Excel
  • Creating and Opening an existing file in Excel
  • How to calculate percent of goal in Excel
  • Freeze and Unfreeze Panes in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning