Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Calculating number of workdays between 2 dates

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Calculating number of workdays between 2 dates

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Calculating number of workdays between 2 dates

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Calculating number of workdays between 2 dates

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Calculating number of workdays between 2 dates

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Calculating number of workdays between 2 dates

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Calculating number of workdays between 2 dates

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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Calculating number of workdays between 2 dates

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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Calculating number of workdays between 2 dates

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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Calculating number of workdays between 2 dates

Hello Ron,

I meant the formula Jacob published.

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

Regards,
Bernd


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Calculating number of workdays between 2 dates

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
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 95
Default Calculating number of workdays between 2 dates

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
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Calculating number of workdays between 2 dates

Hello Michael,

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

Regards,
Bernd
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7,247
Default Calculating number of workdays between 2 dates

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

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Calculating number of workdays between 2 dates


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



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default With start date in cell A1 and end date in cell B1 try the belowformula which

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating number of workdays PO Excel Worksheet Functions 1 August 31st 06 01:40 PM
How do you calculate number of workdays from dates entered? tfleck Excel Worksheet Functions 1 March 25th 05 10:17 PM
How do you calculate number of workdays from dates entered? [email protected] Excel Worksheet Functions 0 March 25th 05 08:55 PM
Calculate the number of workdays between 2 dates Tegid77 Excel Worksheet Functions 2 November 5th 04 12:09 AM
Calculate the number of workdays between 2 dates Tegid77 Excel Worksheet Functions 1 November 4th 04 08:27 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"