Remember Me?

#1
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
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
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
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
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
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
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
Difference - except for Saturdays and Sundays

Usually the Analysis ToolPak is installed by default from the CD but not
--
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
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
Difference - except for Saturdays and Sundays

Nope, looks like it wasn't installed initially as when I select (tick)

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

#6
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
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)

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

#7
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
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
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
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
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
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
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
Difference - except for Saturdays and Sundays

Magic! Thanks so much everyone.

Cheers, Ricky

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Chuck M Excel Worksheet Functions 4 July 5th 07 09:34 PM H Excel Worksheet Functions 7 January 24th 07 04:15 PM John Michl Charts and Charting in Excel 2 August 23rd 06 03:11 PM Jim Long Excel Discussion (Misc queries) 1 November 1st 05 07:13 PM Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM

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