Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • Left Lookup in Excel
  • Get nth match with INDEX / MATCH in Excel
  • Extract all partial matches in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to Use Solver Tool in Excel
  • Get column index in Excel Table
  • Chart Axes in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • Excel Data validation unique values only
  • Data validation must not exist in list
  • Excel Data validation allow weekday only
  • Excel Data validation allow uppercase only
  • Excel Data validation don’t exceed total

Category: Date and Time Functions

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

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

Calculate days remaining in Excel

by

If you need to calculate the days remaining from one date to another, you can use a simple formula that subtracts the earlier date from the later date. See example below; Formula =end_date-start_date Explanation In the example shown, the formula in D5 is: =C5-B5 How this formula works Dates in Excel are just serial numbers …

Continue Reading

Calculate date overlap in days in Excel

by

If you need to calculate the number of days that overlap in two date ranges, then this tutorials is for you. You can use basic date arithmetic, together with the the MIN and MAX functions. See example below; Formula =MAX(MIN(end1,end2)-MAX(start1,start2)+1,0) Explanation In the example shown, the formula in D6 is: =MAX(MIN(end,C6)-MAX(start,B6)+1,0) How this formula works Excel dates …

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

Add days exclude certain days of week in Excel

by

It you need to add days and exclude certain days of week in Excel then this tutorials is for you. To add days to a date while excluding specific days (i.e. exclude Tuesdays and Thursdays, Wednesdays only, etc.) you can use the WORKDAY.INTL function with a special pattern code see the example below: Formula =WORKDAY.INTL(date,days,”pattern”) Explanation …

Continue Reading

Add days to date in Excel

by

It you need to add days to date in Excel then this tutorials is for you. See example below: To add a given number of years to a date, you can use the EDATE function. Formula =date+days Explanation In the example shown, the formula in D5 is: =B5+C5 How this formula works Dates in Excel are …

Continue Reading

Add decimal hours to time in Excel

by

It you need to add decimal hours to time in Excel then this tutorials is for you. See example below. To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function. Formula =time+(hours/24) Explanation In the example shown, the formula in D5 is: =B5+(C5/24) Note: make …

Continue Reading

Add decimal minutes to time in Excel

by

It you need to add decimal minutes to time in Excel then this tutorials is for you. See example below: To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. Formula =time+(minutes/1440) Explanation In the example shown, the formula in D5 is: =B5+(C5/1440) Note: …

Continue Reading

Add months to date in Excel

by

It you need to add months to date in Excel then this tutorials is for you. See example below: To add a given number of years to a date, you can use the EDATE function. Formula =EDATE(date,months) Explanation In the example shown, the formula in D5 is: =EDATE(B5,C5) How this formula works The EDATE function is …

Continue Reading

Add workdays no weekends in Excel

by

It you need to add workdays no weekends in Excel then this tutorials is for you. See example below: Therefore, To add or subtract workdays days to a date, respecting holidays but assuming a 7-day workweek, you can you can use the WORKDAY.INTL function. Formula =WORKDAY.INTL(start_date,days,”0000000″, holidays) Explanation In the example, the formula in D6 is: =WORKDAY.INTL(B6,C6,”0000000″,holidays) …

Continue Reading

Add workdays to date custom weekends in Excel

by

It you need to add workdays to date custom weekends in Excel then this tutorials is for you. See the example below. To add or subtract workdays days to a date so that you can calculate a date in the future or past that skips weekends (customized) and holidays , you can use the WORKDAY.INTL function. Holidays …

Continue Reading

Add years to date in Excel

by

If you need to add years to date in Excel then this tutorials is for you. See the example below . To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions. Formula =DATE(YEAR(date)+years,MONTH(date),DAY(date)) Note: if you need …

Continue Reading

Assign points based on late time in Excel

by

It you need to assign points based on late time in Excel then this tutorials is for you. See the example below. To assign penalty points based on an amount of time late, you can use a nested IF formula. Formula = IF(time<VALUE(“0:05”),0, IF(time<VALUE(“0:15”),1, IF(time<VALUE(“0:30”),2, IF(time<VALUE(“0:60”),3, IF(time<VALUE(“4:00”),4, 5))))) Explanation In the example shown, the formula in E5 …

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

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

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

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
  • Split numbers from units of measure in Excel
  • How to extract nth word from text string in excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to extract substring in Excel
  • How to split text string at specific character in Excel
  • Calculate date overlap in days in Excel
  • Calculate expiration date in Excel
  • Custom weekday abbreviation in Excel
  • Get work hours between dates custom schedule in Excel
  • Add days to date in Excel
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles