How to calculate days between two dates in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

How to calculate days between two dates in Excel

Post by Admin »

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.


Image



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


Image


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.


Image


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.


Image


So great work ahead guys :-)




*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com
Post Reply