|
Countif Across the Midnight Hour
I have looked for the past 3 hours for the answer....I know it is very
simple: I have a column with various times in it. I am attempting to count the number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45. I have correctly (I think) calculated for Day and evening shift. =COUNTIF(D2:D58,"<="&$C$71)-COUNTIF(D2:D58,"<"&$B$71) where D2:D58 is col. containing the times and $C$71 and $B$71 are cells I set up containing the range (6:30 is C71 and 15:30 is B71) for the respective shift. When I attempt this formula across the Mid Shift, I get a -49. Simple math (and maybe its the easiest) tells me out of a range of 57 rows, and Day returns a result of 13 and Eve returns 41, my answer should be 3. What formula do I use for calculating across the midnight hour? |
Countif Across the Midnight Hour
Hi!
When you use a 24 hour clock you should enter midnight as 24:00, not 0:00. Entering midnight as 0:00 evaluates to zero and there are no times <0. You can format 24:00 to appear as 0:00 but the true underlying is not changed. 24:00 evaluates to 1. Excel stores time as a fraction of a day. A day is equal to 1 and midnight starts a new day. So......... G1 = 6:30...............H1 = 15:00 G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00) G3 = 21:45.............H3 = 6:45 Day shift: =COUNTIF(D2:D58,"="&G1)-COUNTIF(D2:D21,""&H1) Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2) Mid shift: =COUNTIF(D2:D58,"="G3)+COUNTIF(D2:D58,"<="H3) Since you have overlapping shift times the total count of all occurrences can be greater than the total number of entries in the range. Biff "meach741" wrote in message oups.com... I have looked for the past 3 hours for the answer....I know it is very simple: I have a column with various times in it. I am attempting to count the number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45. I have correctly (I think) calculated for Day and evening shift. =COUNTIF(D2:D58,"<="&$C$71)-COUNTIF(D2:D58,"<"&$B$71) where D2:D58 is col. containing the times and $C$71 and $B$71 are cells I set up containing the range (6:30 is C71 and 15:30 is B71) for the respective shift. When I attempt this formula across the Mid Shift, I get a -49. Simple math (and maybe its the easiest) tells me out of a range of 57 rows, and Day returns a result of 13 and Eve returns 41, my answer should be 3. What formula do I use for calculating across the midnight hour? |
Countif Across the Midnight Hour
"Biff" wrote in message
... G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00) Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2) Good point about 24:00 but how can any legal shift time be great then H2? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Biff" wrote in message ... Hi! When you use a 24 hour clock you should enter midnight as 24:00, not 0:00. Entering midnight as 0:00 evaluates to zero and there are no times <0. You can format 24:00 to appear as 0:00 but the true underlying is not changed. 24:00 evaluates to 1. Excel stores time as a fraction of a day. A day is equal to 1 and midnight starts a new day. So......... G1 = 6:30...............H1 = 15:00 G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00) G3 = 21:45.............H3 = 6:45 Day shift: =COUNTIF(D2:D58,"="&G1)-COUNTIF(D2:D21,""&H1) Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2) Mid shift: =COUNTIF(D2:D58,"="G3)+COUNTIF(D2:D58,"<="H3) Since you have overlapping shift times the total count of all occurrences can be greater than the total number of entries in the range. Biff "meach741" wrote in message oups.com... I have looked for the past 3 hours for the answer....I know it is very simple: I have a column with various times in it. I am attempting to count the number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45. I have correctly (I think) calculated for Day and evening shift. =COUNTIF(D2:D58,"<="&$C$71)-COUNTIF(D2:D58,"<"&$B$71) where D2:D58 is col. containing the times and $C$71 and $B$71 are cells I set up containing the range (6:30 is C71 and 15:30 is B71) for the respective shift. When I attempt this formula across the Mid Shift, I get a -49. Simple math (and maybe its the easiest) tells me out of a range of 57 rows, and Day returns a result of 13 and Eve returns 41, my answer should be 3. What formula do I use for calculating across the midnight hour? |
Countif Across the Midnight Hour
If D2:D58 contains simple times (without dates) then you can use this formula =SUMPRODUCT(--((D$2:D$58G2)+(D$2:D$58<H2)+(G2H2)=2)) to count the number within each shift where G2 contains the start of the range (e.g. 06:00) and H2 the end of the range (e.g. 15:00) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=530219 |
Countif Across the Midnight Hour
how can any legal shift time be great then H2?
It can't! I just drag copied the first formula. It has no effect but the formula could be simply: =COUNTIF(D2:D58,"="&G2) Biff "Sandy Mann" wrote in message ... "Biff" wrote in message ... G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00) Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2) Good point about 24:00 but how can any legal shift time be great then H2? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Biff" wrote in message ... Hi! When you use a 24 hour clock you should enter midnight as 24:00, not 0:00. Entering midnight as 0:00 evaluates to zero and there are no times <0. You can format 24:00 to appear as 0:00 but the true underlying is not changed. 24:00 evaluates to 1. Excel stores time as a fraction of a day. A day is equal to 1 and midnight starts a new day. So......... G1 = 6:30...............H1 = 15:00 G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00) G3 = 21:45.............H3 = 6:45 Day shift: =COUNTIF(D2:D58,"="&G1)-COUNTIF(D2:D21,""&H1) Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2) Mid shift: =COUNTIF(D2:D58,"="G3)+COUNTIF(D2:D58,"<="H3) Since you have overlapping shift times the total count of all occurrences can be greater than the total number of entries in the range. Biff "meach741" wrote in message oups.com... I have looked for the past 3 hours for the answer....I know it is very simple: I have a column with various times in it. I am attempting to count the number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45. I have correctly (I think) calculated for Day and evening shift. =COUNTIF(D2:D58,"<="&$C$71)-COUNTIF(D2:D58,"<"&$B$71) where D2:D58 is col. containing the times and $C$71 and $B$71 are cells I set up containing the range (6:30 is C71 and 15:30 is B71) for the respective shift. When I attempt this formula across the Mid Shift, I get a -49. Simple math (and maybe its the easiest) tells me out of a range of 57 rows, and Day returns a result of 13 and Eve returns 41, my answer should be 3. What formula do I use for calculating across the midnight hour? |
Countif Across the Midnight Hour
Thanks to all who replied: Sandy, Biff and Daddylonglegs. I used the
SUMIF formula and it worked great. I tried the others and they worked as well. The SUMIF Formula was just easier to copy and paste across my 12 spreadsheets with having to make the corrections for the cell references. I'm sure I will be back with other questions. Thanks again, Tim |
| All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com