Excel difference between two dates in working (5 day weeks)

Hi all,

im trying to get the difference between two dates in working weeks (5 day weeks)

eg
A1
28/10/2011

A2
18/08/2011

=(A1-A2)/7 give us 10.14 which is wrong for what we need, we need it to be a 5 day week not 7 so should give us 10.2

this doable?
Thanks
LVL 1
awilderbeastAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
correct the cell references in the above formula to

=NETWORKDAYS(A2,A1)/5

The syntax for Networkdays() is

=Networkdays(startDate,endDate,[holidays - optional])
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello awilderbeast,

you can use Networkdays() to use only weekdays and exclude weekdays and holidays. In Excel 2003 and earlier, you need to activate the Analysis Toolpak

With your data, the formula would be

=NETWORKDAYS(A6,A3)/5

You want to divide by 5, since that is the number of workdays in the week. The result is 10.4, though, not 10.2

cheers, teylyn
0
 
awilderbeastAuthor Commented:
it comes out as -10.4 on mine, why -?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
suvmitraCommented:
try to use networkdays function if you are using Excel 2007.
0
 
suvmitraCommented:
format the cell as number with decimal place Zero.
0
 
awilderbeastAuthor Commented:
thats great thanks
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> it comes out as -10.4 on mine, why -?

NZ variance maybe. Things are different Down under. :-)

Glad it works for you, and thanks for the grade.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.