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

Data Analysis

  • How To Insert and Customize Sparklines in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to count table columns in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Understanding Pivot Tables in Excel

References

  • How to use Excel MMULT Function
  • How to get address of last cell in range in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • How to get address of first cell in range in Excel
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • How To Create Drop-down List in Excel
  • Excel Data validation require unique number
  • Excel Data validation specific characters only

Category: Date and Time Functions

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

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

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

Sum through n months in Excel

by

This tutorial shows how to Sum through n months in Excel using the example below; Formula =SUM(OFFSET(start,0,0,N,1)) Explanation To sum a set of monthly data through n number of months, you can use a formula based on the SUM and OFFSET functions. In the example shown, the formula in G6 is: =SUM(OFFSET(C5,0,0,G5,1)) How this formula …

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

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

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

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

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 date string to date time in Excel

by

This tutorial shows how to Convert date string to date time in Excel using example below. To convert a date string to a datetime (date with time) you can parse the text into separate components then build a proper datetime. When date information from other systems is pasted or imported to Excel, it may not …

Continue Reading

Count day of week between dates in Excel

by

This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.  Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6 …

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

Pad week numbers with zeros in Excel

by

To pad week numbers (or any number) with zeros using a formula, you can use the TEXT function. Formula =TEXT(WEEKNUM(date,type),”00″) Explanation In the example show, D5 contains this formula: =TEXT(WEEKNUM(B5,21),”00″) Which returns the string “07”. How this formula works The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By …

Continue Reading

Convert date to Julian format in Excel

by

This tutorial shows how to Convert date to Julian format in Excel using example below. If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions. Formula =YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),”000″) Explanation Background “Julian date format” refers to a …

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

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel

Date Time

  • Get day from date in Excel
  • How to calculate quarter from date in Excel
  • How to get year from date in Excel
  • Basic timesheet formula with breaks in Excel
  • Generate series of dates by weekends in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel

General

  • Zoom Worksheet in Excel
  • Sum by group in Excel
  • How to set or clear a print area in Excel Worksheet
  • Common Errors in Excel
  • How to generate random date between two dates in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning