Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference - except for Saturdays and Sundays
Magic! Thanks so much everyone. Cheers, Ricky |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference - except for Saturdays and Sundays
Hello Peo
You can simplify that (very slightly) to: =SUM(INT((WEEKDAY(A1-{2,3,4,5,6})-A1+A2)/7)) Richard On Dec 28, 9:05 pm, "Peo Sjoblom" wrote: 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 ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a way to determine the # of Saturdays in a month | Excel Worksheet Functions | |||
Count Saturdays in a List | Excel Worksheet Functions | |||
Skipping Saturdays and Sunday | Charts and Charting in Excel | |||
Calculate the number of Saturdays or Sundays between 2 dates? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions |