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

Data Analysis

  • How to calculate current stock or inventory in Excel
  • Get column name from index in Excel Table
  • How to count table rows in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to Create Thermometer Chart in Excel

References

  • How to create dynamic named range with OFFSET in Excel
  • Find closest match in Excel
  • How to get address of first cell in range in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel TRANSPOSE Function

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation number multiple 100
  • How To Create Drop-down List in Excel
  • Excel Data validation with conditional list
  • Excel Data validation only dates between

Category: Date and Time Functions

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

How to enter Today’s Date or Static Date and Time in Excel

by

To enter today’s date in Excel, use the TODAY function. To enter the current date and time, use the NOW function. To enter the current date and time as a static value, use keyboard shortcuts. Today and Now 1. To enter today’s date in Excel, use the TODAY function. Note: the TODAY function takes no arguments. This date will …

Continue Reading

How to Calculate Age in Excel

by

To calculate the age of a person in Excel, use the DATEDIF function and the TODAY function. The DATEDIF function has three arguments. 1. Enter the date of birth into cell A2. 2. Enter the TODAY function into cell B2 to return today’s date. 3. The DATEDIF function below calculates the age of a person. Note: fill …

Continue Reading

How to get Weekdays, Working days between Two Dates in Excel

by

Learn how to get the day of the week of a date in Excel and how to get the number of weekdays/working days between two dates. Weekday Function 1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. Apparently, 12/18/2017 falls on a Monday. 2. You can …

Continue Reading

Find Last Day of the Month in Excel

by

To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function. 1. For example, get the date of the last day of the current month. Note: the EOMONTH function returns the serial number of the date. Apply a Date format to display the date. 2. For example, get the date of …

Continue Reading

How to get number of days, weeks, months or years between two dates in Excel

by

To get the number of days, weeks or years between two dates in Excel, use the DATEDIF function. The DATEDIF function has three arguments. 1. Fill in “d” for the third argument to get the number of days between two dates. Note: =A2-A1 produces the exact same result! 2. Fill in “m” for the third argument to …

Continue Reading

How to calculate Quarter of Date in Excel

by

An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is …

Continue Reading

How to calculate Day of the Year in Excel

by

An easy formula that returns the day of the year for a given date. There’s no built-in function in Excel that can do this. 1. To get the year of a date, use the YEAR function. 2. Use the DATE function below to return January 1, 2016. The DATE function accepts three arguments: year, month and day. 3. The …

Continue Reading

How to get Holiday Date from Year in Excel

by

This example teaches you how to get the date of a holiday for any year (2019, 2020, etc). Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of …

Continue Reading

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Convert Excel time to decimal seconds

by

This tutorial shows how to Convert Excel time to decimal seconds using example below. To convert a valid Excel time into decimal seconds, you can multiply by 86400. Formula =A1*86400 Explanation In the example shown, the formula in C6 is: =B6*86400 which returns a value of 60, since there are 60 seconds in 1 minute. How …

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 join date and text together in Excel

by

To join a date with text, you can use concatenation with the TEXT function to control the date format.Generic formula =”text”&TEXT(date,format) Note: To control the date format, we use the TEXT function, which is designed to convert a number into text using a specified number format. Explanation  In the example shown, the formula in E4 is: …

Continue Reading

Generate series of dates by weekends in Excel

by

This tutorials covers how to generates Series of dates increment by weekends from a single start date in Excel. If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. Formula …

Continue Reading

Convert Excel time to Unix time in Excel

by

This tutorial shows how to Convert Excel time to Unix time in Excel using example below. To convert a time in Excel’s format to a Unix time stamp, you can use a formula based on the DATE function. Formula =(A1-DATE(1970,1,1))*86400 > Explanation In the example shown, the formula in C5 is: =(B5-DATE(1970,1,1))*86400 How this formula …

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

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

  • How to return blank in place of #DIV/0! error in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • How to use Excel XOR Function
  • How to use Excel OR Function

Date Time

  • Roll back weekday to Friday base on a particular date in Excel
  • Get work hours between dates in Excel
  • Count birthdays by month in Excel
  • Sum race time splits in Excel
  • Sum through n months in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Running count group by n size in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel

General

  • How to calculate percent sold in Excel
  • AutoRecover file that was never saved in Excel
  • Excel Default Templates
  • Basic text sort formula in Excel
  • How to get random value from list or table in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning