How To Calculate The Number Of Time, Hours, Days, Months, Years Between Two Dates By Excel

When you input the date time in excel, you always need to calculate the date-time between 2 dates. This article will tell you how to calculate the time, hours, days, months, and years between 2 dates in Microsoft excel with or without Excel functions.

1. The Example Worksheet And How To Input The Example Date.

1.1 Below Is This Example Worksheet.

  1. In this example, there are 2 dates in excel cells A2 and B2 like the below table.
    A B C D E F G H I J K L M
    1 Time_1 Time_2 Delta Time Number Delta Time ABS Delta Time Delta Hour Delta Minutes Delta Day Delta Month Delta Year Day Ignore Month Month Ignore Year Day Ignore Year
    2 8/6/2022 8:06:19 8/1/2021 23:31 -369.3578588 -8:35:19 08:35:19 08 35:19 370 12 1 5 0 5
  2. And the other columns’ values after column B are the delta time, hour, month, and year between the first 2 column dates.
  3. I will tell you how to calculate them with or without using excel functions in this article.

1.2 How To Input Example Date Time In Cell A2, B2.

  1. The date-time value in cell A2 is calculated with the excel TEXT() and NOW() function.
  2. The NOW() function will get the current date-time in excel.
  3. The TEXT() function will format the date-time number value with a string format.
  4. Click the cell A2, and input the excel formula =TEXT(NOW(),”m/d/yyyy h:mm:ss”) and press Enter key.
  5. Now it will show the current date-time string text in cell A2.
  6. The date-time value in cell B2 is inputted just by hand.

2. How To Get The Number Of Date Time Between 2 Dates Without Using Function In Excel.

2.1 How To Calculate The Number Of Days Between 2 Dates In Excel.

  1. The cell C2‘s value is the number of days between dates A2 and B2.
  2. You can follow the below steps to calculate it.
  3. Click the cell C2 and input the formula =B2-A2 in it.
  4. Maybe you will find that cell C2‘s value is a date-time string but not a number.
  5. To fix this, you can right-click cell C2, and then click the Format Cells… menu item in the popup menu list to open the Format Cells dialog.
  6. Click the Number tab in the Format Cells dialog.
  7. Click the Number item in the Category list.
  8. Then the C2 value will be changed to a number value.
  9. You may find the number value is a negative number, this is because the B2 date is before the A2 date.

2.2 How To Get The Formated Time String Between 2 Dates In Excel.

  1. If you think the delta time number is not visualized, you can also format the number into a date time string format.
  2. Input the formula =TEXT(B2-A2,”h:mm:ss”) into cell D2, then you can see the delta date time in hh:mm:ss format.
  3. If you do not want to see the negative sign before the date-time string, you can use the ABS() function, and input the formula =TEXT(ABS(B2-A2), “hh:mm:ss”) in cell E2.

2.3 How To Get The Hours, Minutes Between 2 Dates In Excel.

  1. Cell F2 and G2 are the hours, and minutes between 2 dates.
  2. You can input the formula =TEXT(ABS(B2-A2), “hh”) in F2, and input the formula =TEXT(ABS(B2-A2), “mm:ss”) in cell G2.

3. How To Use DATEDIF Function To Get Delta Date Time Between 2 Dates In Excel.

3.1 How To Get Delta Days, Months, Years Between 2 Dates In Excel Using DATEDIF() Function.

  1. The DATEDIF(start_date, end_date, options) function is not a standard function, you need to input it wholely by hand.
  2. The options parameter can have 6 values as below.
  3. “D”: get the delta days number between 2 dates.
  4. “M”: get the delta months number between 2 dates.
  5. “Y”: get the delta years number between 2 dates.
  6. “MD”: get the delta days number between 2 dates, do not calculate year & month.
  7. “YM”: get the delta months number between 2 dates, do not calculate the year.
  8. “YD”: get the delta days number between 2 dates, do not calculate year but calculate month.
  9. Input =DATEDIF(B2,A2,”D”) in cell H2.
  10. Input =DATEDIF(B2,A2,”M”) in cell I2.
  11. Input =DATEDIF(B2,A2,”Y”) in cell J2.
  12. Input =DATEDIF(B2,A2,”MD”) in cell K2.
  13. Input =DATEDIF(B2,A2,”YM”) in cell L2.
  14. Input =DATEDIF(B2,A2,”YD”) in cell M2.
  15. Then you get the above values in each cell.

4. How To Use The DAYS Function To Get Number Of Days Between 2 Dates In Excel.

4.1 Get The Number Of Days Between 2 Dates In Excel Using The DAYS() Function.

  1. You can also use the DAYS(end_date, start_date) function to get the days between 2 dates in excel.
  2. Input =DAYS(B2, A2) in a cell then you can get the delta days between the 2 dates.

5. How To Use The DATE Function To Get Delta Days Between 2 Excel Dates.

  1. The excel DATE(year, month, day) function can create an excel DATE object.
  2. If you know the 2 dates year, month, and day, then you can create 2 excel DATE objects.
  3. Then minus the above 2 DATE object to get delta days between them. Below is an example.
    =DATE(2022,8,8) - DATE(2021,8,10)

6. How To Get Workdays Exclude Weekends Between 2 Excel Dates.

  1. You can use the formula NETWORKDAYS(start_date, end_date, [holidays]) to calculate the workdays number between 2 dates, this will not include the weekends.
  2. The third parameter holidays is a cell list that includes the holidays that will not be calculated as workdays.
  3. Below is an example, suppose cell A1‘s value is 1/1/2021, and cell B1‘s value is 1/1//2022.
  4. Then you can use the formula =NETWORKDAYS(A1,B1) to calculate the workdays between the 2 dates.
  5. If you input 2 holidays such as 10/1/2021 in cell A3, and 7/1/2021 in cell B3, then you can use the formula =NETWORKDAYS(A1,B1,A3:B3) to calculate the workdays between A1, B1 and exclude the holiday in A3 and B3.

7. How To Get The Days Number Between A Date And Today In Excel.

  1. First, you should use the TODAY() function to get today’s value in excel.
  2. Then you can use the formula like =DATE(2022,12,1) – TODAY() to calculate the days between today and the specified date.

8. How To Get The Workday Date From Now After Specified Number Of Days.

  1. You can use the excel WORKDAY(start_date, days, holidays) function to get the date value after the specified number of days from the start_date.
  2. Below is an example, it will calculate the workday’s date after 3 days from today.
    =WORKDAY(TODAY(),3)
    

9. Conclusion.

  1. Excel provides so many date-time-related functions and formulas for you to use.
  2. You can click the Formulas tab —> Date & Time drop-down list to select the date-time formulas to use them.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clicky