On Aug 21, 9:38 pm, Gibbyky2
wrote:
here goes

i use excel 2007 and can calculate number of days between 2 dates using
networkdays function. but my colleagues use older versions of excel and
networkdays function does not work

i want to work out the example below as an example

1st date = 09/10/09
2nd date = 23/10/09

this equals 10 working days, i then want to be able to calculate the number
of days by 7.4 (average hours worked per day)

many thanks

Hi ,

have a look at C. Pearson's "Better NetWordkDays" function: site
http://www.cpearson.com/Excel/BetterNetworkDays.aspx
hope that helps
Have fun
Michael

Hello Michael,

That site uses INDIRECT - thats something I would not like to use.
This command is volatile...

Regards,
Bernd

That site uses INDIRECT - thats something I would not like to use.

The following formula from my
http://www.cpearson.com/excel/DayOfWeekFunctions.aspx page returns the
number of days-of-the-week between StartDate and EndDate.

=((EndDate-MOD(WEEKDAY(EndDate)-DayOfWeek,7)-
StartDate-MOD(DayOfWeek-WEEKDAY(StartDate)+7,7))/7)+1

This will return the number of DayOfWeek days (1 = Sunday, 2 = Monday,
..... 7 = Saturday) between StartDate and EndDate.

To count multiple days of the week, put the day numbers (1 = Sunday, 2
= Monday... 7 = Saturday) in an array where DayOfWeek appears and SUM
the result. Since this is an array formula, you must press CTRL SHIFT
ENTER rather than just ENTER.

The following array formula returns the number of Sundays (=1) and
Saturdays (=7) between StartDate and EndDate.

=SUM(((EndDate-MOD(WEEKDAY(EndDate)-{1,7},7)-
StartDate-MOD({1,7}-WEEKDAY(StartDate)+7,7))/7)+1)

Note that the days of the week, 1 and 7, are enclosed in curly braces
{ }, not parentheses.

To get the number of days other than Sunday and Saturday between
StartDate and EndDate, use

=EndDate-StartDate-SUM(((EndDate-MOD(WEEKDAY(EndDate)-{1,7},7)-
StartDate-MOD({1,7}-WEEKDAY(StartDate)+7,7))/7)+1)

Or, you could just list the working days you want:

=SUM(((EndDate-MOD(WEEKDAY(EndDate)-{2,3,4,5,6},7)-
StartDate-MOD({2,3,4,5,6}-WEEKDAY(StartDate)+7,7))/7)+1)

Subtract 1 from this result if you don't want inclusive dates. E.g, if
the number of days between 5-October and 6-October is 1 day, subtract
1. If you consider the number of days to be 2, don't subtract.

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

I wrote the DayOfWeekFunctions page after I had written the
BetterNetworkdays page, and didn't update BetterNetworkdays with this
revised formula.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 23 Aug 2009 08:29:51 -0700 (PDT), Bernd P
wrote:

Hello Michael,

That site uses INDIRECT - thats something I would not like to use.
This command is volatile...

Regards,
Bernd

You can count weekdays between 2 dates with non-array

=INT((WEEKDAY(StartDate-day)+EndDate-StartDate)/7)

where day is 1 to 7 Sun to Sat, so to count the total number of Mondays
to Fridays that becomes:

=SUM(INT((WEEKDAY(StartDate-{2,3,4,5,6})+EndDate-StartDate)/7))

or an alternative....

=SUM(INT((8-WEEKDAY(EndDate-{2,3,4,5,6}+1)+EndDate-StartDate)/7))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127561