Calculate Date Diff in Excel

tia_kamakshi
tia_kamakshi used Ask the Experts™
on
Hi Experts,

I wanted to calculate no the days count between 2 dates and total working days with 2 dates to exclude non working dates.

Here I have to exclude Friday and Saturday. These are non working days

Please help me in calculating in attatched sheet.

Kind Regards
CalculateDateCount.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I recommend using the networkdays function if you are using excel 2010.  Just enter start and ending dates, and the number of holidays (this can't be standardized as every country has different holidays)

Oh,  NM that wouldn't work then.  (Just finished reading, and you want non standard workdays) Although, you could just list the normal working days that are non-working days for your company as "Holidays".  Unless Sunday's are working days.

Author

Commented:
yes I am using Excel 2010.

Please help me with example. For now I just wanted to exclude friday and saturday only.

Adding holiday to the list might be next step

Please guide

Many Thanks for your reply
NETWORKDAYS.INTL is going to be what you would like to use then.  I'll see if I can get something hammered out on your posted sheet.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Ok,

Just pasted this into the cell and away you go.  

=NETWORKDAYS.INTL(DATE("20"&RIGHT(A2,2),LEFT(RIGHT(A2,5),2),LEFT(RIGHT(A2,8),2)),DATE("20"&RIGHT(B2,2),LEFT(RIGHT(B2,5),2),LEFT(RIGHT(B2,8),2)),7)


To add holidays, it would be like:

=NETWORKDAYS.INTL(DATE("20"&RIGHT(A2,2),LEFT(RIGHT(A2,5),2),LEFT(RIGHT(A2,8),2)),DATE("20"&RIGHT(B2,2),LEFT(RIGHT(B2,5),2),LEFT(RIGHT(B2,8),2)),7,Your Holidays list here in the format of A1:A100)

I had to do a fair amount of text manipulation to get things to come out properly for me, likely due to you not using the same date format as the USA.  It could be that much of the formula is not required if Excel's regional settings are already adjusting for that difference.  Let me know if you have problems.

Author

Commented:
Thanks for your great help

To test, I am using the dates
Thu 19/04/11      Sun 22/04/11
which is bringing 3 days, which should be 2 days.

I don't have any problem, If we have list of holidays in seperate columns, where days can be excluded, if date is present in that columns range.

Please suggest or Please share with me your excel file

Kind Regards
Most Valuable Expert 2013

Commented:
This version should be sufficient

=NETWORKDAYS.INTL(RIGHT(A2,8),RIGHT(B2,8),7)

Note that it counts start and end date so you might want to subtract 1 to get the "difference". Holiday range can be added as final argument.

Note: it's also possible to use a regular NETWORKDAYS function if you want copmpatibility with earlier versions of excel, i.e. this version

=NETWORKDAYS(RIGHT(A2,8)+1,RIGHT(B2,8)+1)

For that one you need to add 1 to holiday range too, e.g. with holidays in H2:H10 use

=NETWORKDAYS(RIGHT(A2,8)+1,RIGHT(B2,8)+1,INDEX(H$2:H$10+1,0))

regards, barry

Commented:
Sample attached you have to set the cell formats for dates as ddd d/m/yyyy and days as general; refer sample
CalculateDateCount.xls
Most Valuable Expert 2013

Commented:
When I tried with those dates

Thu 19/04/11      Sun 22/04/11

both my formula and ScriptAddict's gave me a result of 2.....

regards, barry
I think you'll find that your weekdays are wrong.  

If you check your calandar, 04/19/2011 is actually Tuesday
                                                04/22/2011 is actually Friday

Making 3 days the correct value! :)

If you change the year to 2012.  I think you'll find things come out as expected.

I didn't save my worksheet or I would post it.

Author

Commented:
Thanks, I have tested the formula
=NETWORKDAYS(RIGHT(A2,8)+1,RIGHT(B2,8)+1,INDEX(H$2:H$10+1,0))

with date range

Thu 19/04/11      Sun 22/04/11
The result is bringing 1 day but it should be 2 days

see higlighted column in attached

Regards
CalculateDateCount.xls
Most Valuable Expert 2013
Commented:
As ScriptAddict says, those dates are not correct. 19/04/2011 is a Tuesday and 22/4/2011 is a Friday so as you have 20th and 21st designated as holidays the correct result is 1 - only Tuesday 19th is counted.

This illustrates a flaw in your approach, by having the day as text you can input any day with any date and excel doesn't care. If you input the date as a "true" date and format to show the day that day will always be correct and the calculations will be easier too - do you need to enter the dates as you are?

regards, barry

Author

Commented:
Many thanks for all your great help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial