Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 28th 07, 12:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 11
Default Difference - except for Saturdays and Sundays

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.






  #2   Report Post  
Old December 28th 07, 12:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Difference - except for Saturdays and Sundays

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.
--
David Biddulph

"Rick" wrote in message
...
Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.



  #3   Report Post  
Old December 28th 07, 01:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 11
Default Difference - except for Saturdays and Sundays

Thanks David.

Looks like I'll have to find the original Office CD.

Mmmm, this will be a problem

Cheers,


On Fri, 28 Dec 2007 12:37:26 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.


  #4   Report Post  
Old December 28th 07, 01:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Difference - except for Saturdays and Sundays

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?
--
David Biddulph

"Rick" wrote in message
news
Thanks David.

Looks like I'll have to find the original Office CD.

Mmmm, this will be a problem

Cheers,


On Fri, 28 Dec 2007 12:37:26 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.




  #5   Report Post  
Old December 28th 07, 01:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 11
Default Difference - except for Saturdays and Sundays


Nope, looks like it wasn't installed initially as when I select (tick)
it from the Tools/AddIns menu it asks for the Office CD.

Mind you, if I share the spreadsheet with other users, they might be
in the same predicament as me - no Toolpack installed on their PC.

Can this problem be resolved with a "normal" formula not requiring the
Toolpack?

Thanks, Ricky



On Fri, 28 Dec 2007 13:06:07 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?




  #6   Report Post  
Old December 28th 07, 01:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Difference - except for Saturdays and Sundays

I haven't tested and analysed in great detail, but if your start and finish
dates are guaranteed not to be at weekends you might try
=A2-A1-2*INT((A2-A1)/7)-2*(MOD(A2-A1,7)WEEKDAY(A2,3))
--
David Biddulph

"Rick" wrote in message
...

Nope, looks like it wasn't installed initially as when I select (tick)
it from the Tools/AddIns menu it asks for the Office CD.

Mind you, if I share the spreadsheet with other users, they might be
in the same predicament as me - no Toolpack installed on their PC.

Can this problem be resolved with a "normal" formula not requiring the
Toolpack?

Thanks, Ricky



On Fri, 28 Dec 2007 13:06:07 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?




  #7   Report Post  
Old December 28th 07, 07:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default Difference - except for Saturdays and Sundays

This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.






  #8   Report Post  
Old December 28th 07, 08:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default Difference - except for Saturdays and Sundays

And this does not require volatile functions, courtesy Daniel Maher

=SUM(INT((B1-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Teethless mama" wrote in message
...
This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.








  #9   Report Post  
Old December 28th 07, 09:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default Difference - except for Saturdays and Sundays

Should of course be

=SUM(INT((A2-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
And this does not require volatile functions, courtesy Daniel Maher

=SUM(INT((B1-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Teethless mama" wrote in
message ...
This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.










  #10   Report Post  
Old December 29th 07, 01:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 11
Default Difference - except for Saturdays and Sundays


Magic! Thanks so much everyone.

Cheers, Ricky


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
Need a way to determine the # of Saturdays in a month Chuck M Excel Worksheet Functions 4 July 5th 07 09:34 PM
Count Saturdays in a List H Excel Worksheet Functions 7 January 24th 07 04:15 PM
Skipping Saturdays and Sunday John Michl Charts and Charting in Excel 2 August 23rd 06 03:11 PM
Calculate the number of Saturdays or Sundays between 2 dates? Jim Long Excel Discussion (Misc queries) 1 November 1st 05 07:13 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM


All times are GMT +1. The time now is 09:42 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017