Joined: 25 Sep 2007
|Posted: Sat Jan 07, 2017 4:20 pm Post subject: How to calculate days between two dates in Excel
|So let's start:
To calculate number of days between two given dates, you simply subtract the End Date from the Start Date. However, cell formatting is important here.
In the table below, I have changed the cells format for Cloumn A, B and E. These three columns have date as cell format.
How do you make it? - Select Column A, right mouse click > Format Cells....> from Category > Date > here you select your preferred date format. Do the same for Column B and E.
For the rest Columns C, D, F you don't need to do anything. Their cell format remains General by default.
In the following example, I want to calculate the number of days between 1.1.2017 and 7.1.2017.
Enter in cell C2 =B2-A2. It simply gives you the 'difference' between these two dates. You just did a subtraction.
Now, let's calculate total workdays and net workdays. Our normal working days are from Monday till Friday, i.e. the weekdays. So total workdays will consider all the weekdays available between your two specified dates. However, there are holidays in between. Net workdays will exclude all the holidays, so you will have the net working days remain only.
By the way, Holidays are local, depends on the country you live. In Germany, January 1 is New Year's holiday and January 6 is a religious holiday.
For the last two calculations(Total workdays and net workdays), I will uses Excels's NETWORKDAYS function. Its syntax is:
NETWORKDAYS(start_date, end_date, [holidays])
while start day and end day are a must, holidays are optional.
By the way, NETWORKDAYS function takes into consideration both start date and end date + all the days in between.
To calculate total workdays, enter in cell D2
=NETWORKDAYS(A2;B2) - Number of workdays between the start (1.1.2017) and end date (7.1.2013).
The calculation in cell D2 excludes all Saturdays and Sundays, but includes the weekdays. Any holiday that falls within these weekdays is also included here as working day. So your total workdays are 5.
To calculate net workdays, enter in cell F2
=NETWORKDAYS(A2;B2;E2:E3) - Number of net workdays between the start (1.1.2017) and end date (1.1.2017), with the two holidays (1.1.2017 and 6.1.2017) as non-working days.
The calculation in cell F2 excludes all Saturdays and Sundays + any holiday that falls within the weekdays. So you have only net working days remain within the given period. It's 4.
Interestingly, this year January 1 is Sunday, it's weekend and a non-working day. So shall I include it in my holiday array while calculating my net working days? - The answer is 'yes'.
Because , if it would be 2016, January 1 was Friday, then January 6 was Wednesday (both are working days in general but given as holidays in Germany), so your calculated net working days will be 3, whereas in 2017 you have 4 net working days in the same given period.
So great work ahead guys
Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.
For comments of suggestions, please contact us: email@example.com