Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count times between 2 times and 2 dates
This is difficult and I'm lost! Here is the data (omitting not needed)
A D E Control # Date Time 2 questions: 1. What formula would I use if I wanted to find out the number of #'s ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year). 1.a IS there a way not to count diplicates of the control # only... it would be ok to count duplicate dates, just not duplicate of control #'s - which could occur unfortunately becuase of the way the data is used. 2. What formula would I use if I wanted to find out the number of #'s occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06? 2.a SAME as 1.a I tried using the sumproduct formula and am completely screwing that up... please help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count times between 2 times and 2 dates
Assume data in the table (cols A, D, E) is from row2 to row100,
col D contains real dates, col E contains time expressed as text numbers, eg: "0000", "0100", etc Use 2 empty cols to the right, say, cols G & H Put in G2: =A2&"_"&D2 Put in H2: =IF(COUNTIF($G$2:G2,G2)1,"","x") Select G2:H2, copy down to H100 (Col H will flag unique "Control # - Date" with an "x") Then, to extract the the number of Control #'s ocurring on Monday between 1/1/06 and 3/31/06 (counting only unique "Control # - Date") we could put in say, I2: =SUMPRODUCT((H2:H100="x")*(WEEKDAY(D2:D100)=1)*(D2 :D100= --"1-Jan-2006")*(D 2:D100<= --"31-Mar-2006")) And to retrieve the number of Control #'s occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06 (again, counting only unique "Control # - Date") we could put in say, J2: =SUMPRODUCT((H2:H100="x")*(E2:E100= "0000")*(E2:E100<= "0400")*(D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006")) Adapt the ranges to suit -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jeremy Ellison" wrote in message ... This is difficult and I'm lost! Here is the data (omitting not needed) A D E Control # Date Time 2 questions: 1. What formula would I use if I wanted to find out the number of #'s ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year). 1.a IS there a way not to count diplicates of the control # only... it would be ok to count duplicate dates, just not duplicate of control #'s - which could occur unfortunately becuase of the way the data is used. 2. What formula would I use if I wanted to find out the number of #'s occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06? 2.a SAME as 1.a I tried using the sumproduct formula and am completely screwing that up... please help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count times between 2 times and 2 dates
And if we don't want to count only unique "Control # - Date"'s
just remove this condition from the formula: .. (H2:H100="x")* .. viz., use: In I2: =SUMPRODUCT((WEEKDAY(D2:D100)=1)* (D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006")) In J2: =SUMPRODUCT((E2:E100= "0000")*(E2:E100<="0400")* (D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate times between dates and times in Excel | Excel Discussion (Misc queries) | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
Count No. of times Dates are repeated | Excel Discussion (Misc queries) | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions |