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.
- 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
- And the other columns’ values after column B are the delta time, hour, month, and year between the first 2 column dates.
- 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.
- The date-time value in cell A2 is calculated with the excel TEXT() and NOW() function.
- The NOW() function will get the current date-time in excel.
- The TEXT() function will format the date-time number value with a string format.
- Click the cell A2, and input the excel formula =TEXT(NOW(),”m/d/yyyy h:mm:ss”) and press Enter key.
- Now it will show the current date-time string text in cell A2.
- 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.
- The cell C2‘s value is the number of days between dates A2 and B2.
- You can follow the below steps to calculate it.
- Click the cell C2 and input the formula =B2-A2 in it.
- Maybe you will find that cell C2‘s value is a date-time string but not a number.
- 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.
- Click the Number tab in the Format Cells dialog.
- Click the Number item in the Category list.
- Then the C2 value will be changed to a number value.
- 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.
- If you think the delta time number is not visualized, you can also format the number into a date time string format.
- 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.
- 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.
- Cell F2 and G2 are the hours, and minutes between 2 dates.
- 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.
- The DATEDIF(start_date, end_date, options) function is not a standard function, you need to input it wholely by hand.
- The options parameter can have 6 values as below.
- “D”: get the delta days number between 2 dates.
- “M”: get the delta months number between 2 dates.
- “Y”: get the delta years number between 2 dates.
- “MD”: get the delta days number between 2 dates, do not calculate year & month.
- “YM”: get the delta months number between 2 dates, do not calculate the year.
- “YD”: get the delta days number between 2 dates, do not calculate year but calculate month.
- Input =DATEDIF(B2,A2,”D”) in cell H2.
- Input =DATEDIF(B2,A2,”M”) in cell I2.
- Input =DATEDIF(B2,A2,”Y”) in cell J2.
- Input =DATEDIF(B2,A2,”MD”) in cell K2.
- Input =DATEDIF(B2,A2,”YM”) in cell L2.
- Input =DATEDIF(B2,A2,”YD”) in cell M2.
- 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.
- You can also use the DAYS(end_date, start_date) function to get the days between 2 dates in excel.
- 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.
- The excel DATE(year, month, day) function can create an excel DATE object.
- If you know the 2 dates year, month, and day, then you can create 2 excel DATE objects.
- 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.
- 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.
- The third parameter holidays is a cell list that includes the holidays that will not be calculated as workdays.
- Below is an example, suppose cell A1‘s value is 1/1/2021, and cell B1‘s value is 1/1//2022.
- Then you can use the formula =NETWORKDAYS(A1,B1) to calculate the workdays between the 2 dates.
- 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.
- First, you should use the TODAY() function to get today’s value in excel.
- 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.
- 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.
- Below is an example, it will calculate the workday’s date after 3 days from today.
- Excel provides so many date-time-related functions and formulas for you to use.
- You can click the Formulas tab —> Date & Time drop-down list to select the date-time formulas to use them.