Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

How to calculate days between two dates in Excel

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 272

PostPosted: Sat Jan 07, 2017 4:20 pm    Post subject: How to calculate days between two dates in Excel Reply with quote

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 Smile




*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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group