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

Data Analysis

  • How to create running total in an Excel Table
  • How to add Trendline to a chart in Excel
  • How To Compare Two Lists in Excel
  • Get column name from index in Excel Table
  • Working With Tables in Excel

References

  • Two-column Lookup in Excel
  • Last row number in range
  • How to get last column number in range in Excel
  • How to get address of last cell in range in Excel
  • To count total rows in a range in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only

Category: Date and Time Functions

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

Display the current date and time in Excel

by

This tutorial show how to Display the current date and time in Excel using the example below. If you need to display the current date and time in a worksheet, you can use the NOW function. The date and time will update whenever the worksheet is recalculated or opened. Formula =NOW() Explanation of how this formula …

Continue Reading

Get day name from date in Excel

by

If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date or to convert the date into a day name, there are several options depending on your needs. Formula TEXT(B4,”dddd”) Explanation Do you just want to display the day name? If you only want to display a day name, you don’t need a …

Continue Reading

Calculate expiration date in Excel

by

If you need to calculate an expiration in the future, you can use a variety of formulas. See example below; Formula =A1+30 // 30 days Explanation In the example shown, the formulas used in column D are: =B5+30 // 30 days =B5+90 // 90 days =EOMONTH(B7,0) // end of month =EDATE(B8,1) // next month =EOMONTH(B7,0)+1 …

Continue Reading

Display the current date in Excel

by

This tutorial show how to Display the current date in Excel using the example below. If you need to display the current date in a worksheet, you can use the TODAY function. This date will update whenever the worksheet is recalculated or opened.  Formula =TODAY() Explanation of how this formula works Simply enter the formula above …

Continue Reading

Check If Two Dates are same month in Excel

by

This tutorial shows how to compare dates in Excel. To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. Formula =MONTH(date1)=MONTH(date2) Explanation In the example shown, the formula in cell D6 is: =MONTH(B6)=MONTH(C6) How the formula works In this case, …

Continue Reading

Calculate number of hours between two times in Excel

by

This tutorials shows how to calculate number of hours between two times in Excel. To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary …

Continue Reading

Display Days until expiration date in Excel

by

This tutorial show how to Display Days until expiration date in Excel using the example below. To calculate the days until an expiration date, you can use a simple formula based on the TODAY function. Formula =A1-TODAY() Explanation of how this formula works In the example shown, the formula in C6 is: =B6-TODAY() Dates in Excel …

Continue Reading

Get month name from date in Excel

by

If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs. Formula =TEXT(date,”mmmm”) Explanation To convert the date into a month name If you want to convert the date value to a text value, you can use the TEXT function with a custom number …

Continue Reading

Basic Overtime Calculation Formula in Excel

by

If you need to find basic overtime calculation formula in Excel then this tutorials is for you. See the example below. To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. Formula =(reg_hrs*rate)+(ot_hrs*rate*1.5) Explanation In formula in cell I5 is: =(F5*H5)+(G5*H5*1.5) How this formula works Note: it’s important …

Continue Reading

Calculate retirement date in Excel

by

If you need to calculate retirement date in Excel, then this tutorials is for you. To calculate a retirement date based on a birth date, you can use the EDATE function. See example below; Formula =EDATE(A1,12*60) Explanation In the example shown, the formula in D6 is: =EDATE(C6,12*60) How this formula works The EDATE function is fully automatic, and will …

Continue Reading

Get month from date in Excel

by

If you need to extract the month from a date, you can use the MONTH function. Note: The date in the  formula below must be in a form that Excel recognizes as a valid date.  Formula =MONTH(date) Explanation The MONTH function takes just one argument, the date from which you want to extract the month. …

Continue Reading

Add business days to date in Excel

by

If you need to add or subtract business days (workdays) to a date so that you can calculate a date in the future or past that skips weekends (and holidays),  then this tutorials is for you. The example below shows how to  Add business days to date in Excel. You can use the WORKDAY function. Holidays …

Continue Reading

Calculate years between dates in Excel

by

If you want to calculated the number of years between two dates,  then this tutorials is for you. See example below; To calculated the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. Here are a few examples …

Continue Reading

Display Date is workday in Excel

by

This tutorial show how to Display Date is workday in Excel using the example below. To determine if a date is a workday or not, you can use a formula based on the WORKDAY function. =WORKDAY(date-1,1,holidays)=date Explanation of how this formula works In the example shown, the formula in C5 is: =WORKDAY(B5-1,1,holidays)=B5 which returns TRUE, since Monday, …

Continue Reading

Get last working day in month in Excel

by

If you need to determine the last working day of a given month and year, then this tutorials is for you. To get the last working day in a month, you can use the WORKDAY function together with the EOMONTH function. See example below: Formula =WORKDAY(EOMONTH(date)+1,-1) > In the example, the formula in C4 is: …

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 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

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

Posts navigation

  • Previous
  • 1
  • 2
  • 3
  • 4

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

  • Extract multiple matches into separate rows in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Check If Two Dates are same month in Excel
  • Get days, months, and years between dates in Excel
  • Next biweekly payday from date in Excel
  • Get project end date in Excel
  • Series of dates by day

Grouping

  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Basic text sort formula in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • How to create dynamic named range with INDEX in Excel
  • How to calculate percent variance in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning