Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula help
I am trying to calculate the number of days in a time period excluding
Sundays, (i.e., 2/29/08 minus 2/23/08 = 5 ) Can anyone help me? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula help
(i.e., 2/29/08 minus 2/23/08 = 5 )
The correct result is 6. A1 = 2/23/2008 B1 = 2/29/2008 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<7)) -- Biff Microsoft Excel MVP "Di" <Di @discussions.microsoft.com wrote in message ... I am trying to calculate the number of days in a time period excluding Sundays, (i.e., 2/29/08 minus 2/23/08 = 5 ) Can anyone help me? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula help
=B1-A1-SUM((WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1)*1)
This is an array formula and must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200773 "Di" wrote: I am trying to calculate the number of days in a time period excluding Sundays, (i.e., 2/29/08 minus 2/23/08 = 5 ) Can anyone help me? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula help
On Fri, 14 Mar 2008 09:28:00 -0700, Di <Di @discussions.microsoft.com wrote:
I am trying to calculate the number of days in a time period excluding Sundays, (i.e., 2/29/08 minus 2/23/08 = 5 ) Can anyone help me? Thanks =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<1)) will return a count of all the days from and INCLUDING A1 and A2 that are not Sundays. To exclude the starting date, merely subtract 1 from that value: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<1))-1 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |