Search results

Hi, I understand you want to count the number of occurrences of the file in Sheet2. Please consider pivoting this data. Jeanbar

Sam, Thanks for your comment. The formula for available staff (based on the above information) is: {=SUM((RC3>=INDEX(T_SHIFT;;3))*(RC3<=INDEX(T_SHIFT;;4))*(RC4>=INDEX(T_SHIFT;;3))*(RC4<=INDEX(T_SHIFT;;4))*(INDEX(T_SHIFT;;5)="ON"))} CSE formula, of course!

Narajan, I understood it the other way round: the status of "ON" means that the person is on-duty in this particular shift and off-duty outside the period. So in the shift 0300-0600 there is nobody available with the data provided. Perhaps Pjb could confirm ?

Interesting, My try (less text processing, more maths): =RC1-(MOD(RC1*100,10)-CHOOSE(MOD(RC1*100,10)+1,0,5,5,5,5,5,9,9,9,9))/100 Jeanbar

Narajan, Are you sure of your last line ? Hours Total Staff Actual Count ... 0300-0600 ..... 7 ..... 2 Shoudn't it be zero ?

Pjb, I posted an answer to your question in another post http://chandoo.org/forums/topic/find-if-2-sets-of-times-overlap-calculate-time-overlapped-even-through-midnight?replies=19#post-14236 The crux of the problem is to manage the shift period crossing 2 days.

Narajan, You are right! Thanks for pointing this out. I tried to remove the extra text but failed to edit my post. (Sorry for that). However, the beginning of the post do answer Sam's question (before the tables' description)

As NARAYANK991 explained, the data and time is a decimal number made of 2 parts: - The date itself is the integer part of this number - The time is the decimal part of this number So the data number 40831.375 is "15/10/2011 09:00:00" where - The integer part (40831) is "15/10/2011" =...