| How to calculate number of days in Excel | 您所在的位置：网站首页 › 胡萝卜含有淀粉吗l › How to calculate number of days in Excel |
How to calculate number of days in Excel
|
If you want to know how to use Excel to count days between two dates, then this post is going to help you. There may be times when you need to calculate the number of days between two given dates while analyzing some financial data. Excel is an amazing tool that can do that for you in a matter of seconds. How? By using Excel Functions! Excel offers a bunch of useful functions that allow you to quickly find the count, sum, average, maximum value, minimum value, etc., for a range of cells. It also offers some text functions and financial functions that are worth checking.
In this post, we are going to show you 5 different methods to count days between two Dates in Excel. They are: Using SubtractionUsing the DAYS functionUsing the DATEDIF functionUsing the NETWORKDAYS functionUsing the TODAY functionLet us have a detailed look at all the above methods. 1] Using Subtraction
Subtraction is the easiest way to count days between two dates in Excel. You can use the arithmetic operator – (minus sign) to subtract one date from another to find the number of days between them. Let’s say we have an Excel sheet with some sample dates listed in two columns, Column A and Column B. The dates in Column B precede the dates in Column A. The third column, Column C, will display the count of days when we subtract the value of Column A from the value of Column B, for each row in the spreadsheet. The following steps describe the process in detail: Place your cursor in cell C3.In the formula bar, type =B3-A3.Press the Enter key. Excel will calculate the number of days between the dates entered in cells B3 and A3 and display the result in cell C3.Take your mouse pointer to the lower-right corner of cell C3. It will turn into a + (plus) symbol.Click, hold, and drag the cursor till cell C6. This action will copy the formula of cell C3 to cells C4, C5, and C6 as well, displaying results for all the dates we have taken into consideration.Note: While using Subtraction, always write the End Date before the Start Date. 2] Using the DAYS function
DAYS is a date function in Excel that calculates the difference between two given dates in days. It can recognize dates passed as ‘strings’ if they can be parsed as valid dates in Excel. Syntax DAYS(end_date, start_date)end_date is the last date providedstart_date is the first date providedNow for the same example described above, we can use the DAYS formula to count the days as follows: Keeping the focus on cell C3, type =DAYS(B3, A3) in the formula bar.Press the Enter key. Days count between dates in cells A3 and B3 will appear in C3.Copy the same formula to cells C4, C5, and C6 through the mouse cursor drag method, as explained above.Tip: If your accounting system is based on a 360-day year (twelve 30-day months), you may use the DAYS360 function to count the number of days. 3] Using the DATEDIF function
The DATEDIF function is an advanced version of the DAYS function. It calculates the difference between two date values based on a specified interval, such as days, months, or years. It is useful in formulas that involve age-based calculations. Syntax DATEDIF(start_date, end_date, unit)start_date is the first or start date of the given period.end_date is the last date of the given period.unit is the information that you would like to get. For example, if you want the DATEDIF function to calculate the number of days, you can enter D in place of the unit. Similarly, you can enter M for months and Y for years. You can also enter a combination of two units, such as YM. This will calculate the difference in months, ignoring the years and the days.Now taking the same example as above, follow these steps to use the DATEDIF function to count days in Excel: Place your cursor in cell C3.Double click and type =DATEDIF(A3, B3, “D”)Press the Enter key. The results will be displayed in cell C3.Now again take your cursor to the lower-right corner of cell C3, and click and drag it to cell C6 to view all the results.Also Read: How to count words in Microsoft Excel. 4] Using the NETWORKDAYS function
NETWORKDAYS is another useful function through which you can use Excel to find days between two dates. It calculates the number of whole working days between two given dates. While calculating the number of days between two given dates, it automatically excludes the weekends (Saturday, Sunday) and optionally excludes any other holidays provided as dates (state, federal, and floating holidays). Syntax NETWORKDAYS(start_date,end_date,[holidays])start_date argument takes the value of the start date.end_date argument takes the value of the end date.[holidays] is a reference to one or dates to be counted as non-working days.Now let’s say we provide a list of holidays (other than weekends) for the same set of dates as shown in the above example. We can use the NETWORKDAYS function to calculate working days between the given dates as follow: Place the cursor in cell D6.In the formula bar, type =NETWORKDAYS(A6,B6,D10:D11). As you can see in the above screenshot, the count of days has now been reduced from 30 to 22 (excluding 4 Saturdays, 4 Sundays, and 1 holiday which is occurring on Sunday).Now double click on cell D5 and type =NETWORKDAYS(A5,B5,D10:D11).Press the Enter key. The function now returns 4, excluding 1 Sunday and 1 holiday.Notes: The NETWORKDAYS function includes the start_date in the count if it happens to be a weekday.If you need to provide custom weekends (for example, if you want Excel to mark Wednesday as a weekend and not Saturday, Sunday), then you should use the NETWORKDAYS.INTL function. This function allows you to pass a ‘weekend’ argument to set which days are considered weekends.5] Using the TODAY function
The TODAY function can be used to calculate the number of days between a past date or a future date, and the current date. By default, it returns the current date (if the cell format is set to General). Syntax Today()Let’s say the current date is 28 September 2022. And we have the date value 04 September 2022 in cell A17 and the date value 30 September 2022 in cell A18. To calculate the number of days between today and 04 September 2022 (which is a past date), we will use the formula =TODAY()-A17. The function returns 24 as the resultant value. Similarly, to calculate the number of days between today and 30 September 2022 (which is a future date), we will use the formula =A18-TODAY(). The function returns 2 as the resultant value. That’s it! Hope you enjoyed knowing these easy tips to use Excel to count days between two dates. Do let us know in the comments if you have any questions. What is the formula for days between two dates?Multiple Excel formulas can be used to calculate the days between 2 given dates. These include DAYS, DATEDIF, NETWORKDAYS, and TODAY functions. To know how these functions can be used to count days, refer to the above post. You may also use the Subtraction operator (-) to count days between two dates, as explained in this post. Why is days formula not working in Excel?If you’re getting the #VALUE! error while using the DAYS function in Excel, there could be a problem with the date format being used. If the argument is being passed in form of text (for example September 28 2022), Excel will not recognize it and throw a #VALUE! error. To resolve this issue, make sure you enter the date in a valid Excel Date format (for example 09/28/2022) or Text format (28-Sep-2022). Read Next: How to multiply numbers in Single or Multiple cells in Excel. 
|
【本文地址】
| 今日新闻 |
| 推荐新闻 |
| 专题文章 |