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

With start date in cell A1 and end date in cell B1 try the below formula
which use WEEKDAY and MIN functions...Format the formula cell to general if
it turns out to be in date format

=(B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2))

If this post helps click Yes
---------------
Jacob Skaria


"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

Many thanks

works a treat :-)

"Jacob Skaria" wrote:

With start date in cell A1 and end date in cell B1 try the below formula
which use WEEKDAY and MIN functions...Format the formula cell to general if
it turns out to be in date format

=(B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2))

If this post helps click Yes
---------------
Jacob Skaria


"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

Sorry jacob

it works out the correct number of days how do i calculate the amount of
hours worked er day by the number of days
IE 01/08/09 to 15/08/09= 10 working days* 7.4 (where 7.4 is hours worked and
..4 percentage of hour worked) should equal 74

???

"Jacob Skaria" wrote:

With start date in cell A1 and end date in cell B1 try the below formula
which use WEEKDAY and MIN functions...Format the formula cell to general if
it turns out to be in date format

=(B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2))

If this post helps click Yes
---------------
Jacob Skaria


"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

Try..
=((B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2)))*7.4

If this post helps click Yes
---------------
Jacob Skaria


"Gibbyky2" wrote:

Sorry jacob

it works out the correct number of days how do i calculate the amount of
hours worked er day by the number of days
IE 01/08/09 to 15/08/09= 10 working days* 7.4 (where 7.4 is hours worked and
.4 percentage of hour worked) should equal 74

???

"Jacob Skaria" wrote:

With start date in cell A1 and end date in cell B1 try the below formula
which use WEEKDAY and MIN functions...Format the formula cell to general if
it turns out to be in date format

=(B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2))

If this post helps click Yes
---------------
Jacob Skaria


"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, Jacob. From your Formula, could you exclude Sat & Sun as well? Really appreciate your help

On Friday, August 21, 2009 3: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


On Friday, August 21, 2009 3:42 PM Jacob Skaria wrote:

With start date in cell A1 and end date in cell B1 try the below formula
which use WEEKDAY and MIN functions...Format the formula cell to general if
it turns out to be in date format

=(B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2))

If this post helps click Yes
---------------
Jacob Skaria


"Gibbyky2" wrote:


On Friday, August 21, 2009 3:52 PM Gibbyky2 wrote:

Many thanks

works a treat :-)

"Jacob Skaria" wrote:


On Friday, August 21, 2009 4:00 PM Gibbyky2 wrote:

Sorry jacob

it works out the correct number of days how do i calculate the amount of
hours worked er day by the number of days
IE 01/08/09 to 15/08/09= 10 working days* 7.4 (where 7.4 is hours worked and
.4 percentage of hour worked) should equal 74

???

"Jacob Skaria" wrote:


On Friday, August 21, 2009 4:57 PM Gord Dibben wrote:

NETWORKDAYS function has been around through many versions of Excel

In older versions it is part of the Analysis Toolpak which must be loaded
through ToolsAdd-ins.

Then it will work.


Gord Dibben MS Excel MVP


On Friday, August 21, 2009 11:45 PM Jacob Skaria wrote:

Try..
=((B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2)))*7.4

If this post helps click Yes
---------------
Jacob Skaria


"Gibbyky2" wrote:


On Saturday, August 22, 2009 7:04 AM Ron Rosenfeld wrote:

I wonder how you obtain a result of 10 working days.

If I plug those dates into the NetWorkdays formula, I get 11 days. Are you not
counting the start or end date?

If you want a formula for earlier versions, similar to NetWorkdays, that also
includes the possibility of adding Holidays, you could use something like:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&":"&En dDate)),2)<6)*
ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Ho lidays,0)))

Note that for the older versions of Excel, this formula will fail for dates
after about 5 June 2079

If you want to multiply this value by 7.4, then just:

=7.4*SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&": "&EndDate)),2)<6)*
ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Ho lidays,0)))

If you do not want to use the Holidays argument, then the second line above can
be replaced by a 1:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&":"&En dDate)),2)<6)*1)

Replace the Names in the formula above with Named ranges, or with the actual
range references.
--ron


On Saturday, August 22, 2009 12:45 PM Ron Rosenfeld wrote:

Your posting comes through as a reply to my suggestion. However, I do not see
the formula I posted on the page you referenced.

As to whether or not the first day should be included, that depends on whether
you want to do a simple subtraction, or if you want to mimic the NETWORKDAYS
function.

Since the OP was looking for an alternative to NETWORKDAYS, which was not
working for his users who had an earlier version of Excel (and presumably
did not have the ATP installed), I would have thought he would want to INCLUDE
both the first and last days as does the NETWORKDAYS function.
--ron


On Saturday, August 22, 2009 4:29 PM Ron Rosenfeld wrote:

OIC.

The threading and the lack of a quote from Jacob's post confused me.

Best wishes,
--ron


On Sunday, August 23, 2009 2:33 PM Bernd P wrote:

Hello,

That's a formula I published a long time ago.

I also explained its features:
http://sulprobil.com/html/date_formulas.html

The first day is (intentionally!) not taken into account.

Regards,
Bernd


On Sunday, August 23, 2009 2:33 PM Bernd P wrote:

Hello Ron,

I meant the formula Jacob published.

A NETWORKDAYS-equivalent approach (without holidays, though) I have
mentioned as well.

Regards,
Bernd


On Sunday, August 23, 2009 2:33 PM Michael.Tarnowski wrote:

wrote:

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


On Sunday, August 23, 2009 2:33 PM Bernd P wrote:

Hello Michael,

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

Regards,
Bernd


On Sunday, August 23, 2009 3:43 PM Chip Pearson wrote:

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 do not 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, do not 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 did not 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 Sunday, August 23, 2009 7:02 PM barry houdini wrote:

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

NETWORKDAYS function has been around through many versions of Excel

In older versions it is part of the Analysis Toolpak which must be loaded
through ToolsAdd-ins.

Then it will work.


Gord Dibben MS Excel MVP


On Fri, 21 Aug 2009 12:38:04 -0700, 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

On Fri, 21 Aug 2009 12:38:04 -0700, 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

I wonder how you obtain a result of 10 working days.

If I plug those dates into the NetWorkdays formula, I get 11 days. Are you not
counting the start or end date?

If you want a formula for earlier versions, similar to NetWorkdays, that also
includes the possibility of adding Holidays, you could use something like:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&":"&En dDate)),2)<6)*
ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Ho lidays,0)))

Note that for the older versions of Excel, this formula will fail for dates
after about 5 June 2079

If you want to multiply this value by 7.4, then just:

=7.4*SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&": "&EndDate)),2)<6)*
ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Ho lidays,0)))

If you don't want to use the Holidays argument, then the second line above can
be replaced by a 1:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(StartDate&":"&En dDate)),2)<6)*1)

Replace the Names in the formula above with Named ranges, or with the actual
range references.
--ron

Hello,

That's a formula I published a long time ago.

I also explained its features:
http://sulprobil.com/html/date_formulas.html

The first day is (intentionally!) not taken into account.

Regards,
Bernd

On Sat, 22 Aug 2009 08:40:00 -0700 (PDT), Bernd P wrote:

Hello,

That's a formula I published a long time ago.

I also explained its features:
http://sulprobil.com/html/date_formulas.html

The first day is (intentionally!) not taken into account.

Regards,
Bernd

Your posting comes through as a reply to my suggestion. However, I do not see
the formula I posted on the page you referenced.

As to whether or not the first day should be included, that depends on whether
you want to do a simple subtraction, or if you want to mimic the NETWORKDAYS
function.

Since the OP was looking for an alternative to NETWORKDAYS, which wasn't
working for his users who had an earlier version of Excel (and presumably
didn't have the ATP installed), I would have thought he would want to INCLUDE
both the first and last days as does the NETWORKDAYS function.
--ron

Hello Ron,

I meant the formula Jacob published.

A NETWORKDAYS-equivalent approach (without holidays, though) I have
mentioned as well.

Regards,
Bernd

On Sat, 22 Aug 2009 12:51:50 -0700 (PDT), Bernd P wrote:

Hello Ron,

I meant the formula Jacob published.

A NETWORKDAYS-equivalent approach (without holidays, though) I have
mentioned as well.

Regards,
Bernd

OIC.

The threading and the lack of a quote from Jacob's post confused me.

Best wishes,
--ron

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