Calculating number of workdays between 2 dates

[Gibbyky2]

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

 

[Jacob Skaria]

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

 

[Gibbyky2]

Many thanks

works a treat

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

 

[Gibbyky2]

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

???

 

[Gord Dibben]

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 Tools>Add-ins.

Then it will work.


Gord Dibben MS Excel MVP

 

[Jacob Skaria]

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

 

[Ron Rosenfeld]

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&":"&EndDate)),2)<6)*
ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,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)),Holidays,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&":"&EndDate)),2)<6)*1)

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

 

[Bernd P]

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

 

[Ron Rosenfeld]

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

 

[Bernd P]

Hello Ron,

I meant the formula Jacob published.

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

Regards,
Bernd

 

[Ron Rosenfeld]

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

 

[Michael.Tarnowski]

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

 

[Bernd P]

Hello Michael,

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

Regards,
Bernd

 

[Chip Pearson]

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)

 

[barry houdini]

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 Monday
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)

 

[Tariq]

Hi, Jacob. From your Formula, could you exclude Sat & Sun as well? Really appreciate your help