Returning a value if the time falls between two intervals

[vamshi57]

Hi ,

I'm the Held desk manager . I'll have my folks logging in every time interval and work for 9hrs. Now I wanted to see at any given point a day the number of folks I have on my desk .

I have created table " A " and I'm looking for a formulae so that I get my data like table " B " .


To give more insight about table '" B " - During 00:00 - 00:30 , I'll have 41 people slowly goes down as people log off..

Thanks in Advance !!

[shg]

First, split col C into two columns, with the start time in col C, and this formula in D4 and copied down:

=C4 + 9/24

Then in I4 (allowing for the inserted column) and copy down,

=SUMPRODUCT( ($C$4:$C$13<=G4) * ($D$4:$D$13>H4) * $B$4:$B$13)

Please Login or Register  to view this content.

[vamshi57]

Hi ,

Thanks for your reply . If I do that , I'm getting value 1 during "00:00 - 00:30 " where as it should be 41 . as I'll have 41 people during that time . Same goes with other slots as well .

[DonkeyOte]

Based on shg's proposed layout (ie splitting start/end times into 2 separate columns) one approach could be:

Please Login or Register  to view this content.

[vamshi57]

I tried this fomula and now I'm getting "0's " . Infact previous ( Sumproduct ) formula worked BUT it's working ONLY from "13:00 - 13:30 " interval and not before that .

[DonkeyOte]

I tried this fomula and now I'm getting "0's "

Are you using CTRL + SHIFT + ENTER to commit the Array formula as advised (in bold and red font) ?

Infact previous ( Sumproduct ) formula worked

I believe shg's SUMPRODUCT will only return number of staff working during the window where the shift worked did not cross midnight, hence for 00:00 -> 00:30 return of 1 rather than 41 (the 4,15,9,10,2 are all ignored)

[shg]

I believe shg's SUMPRODUCT will only return

It's true, I completely missed that. In the unlikely case that DO's doesn't give you what you need, holler.

[vamshi57]

All I need is , Number of staff during any window . I'm also attaching a sheet where I have tried and it didn't work .

[DonkeyOte]

see attached.....

(same formula as advised in my OP based on original file)

[vamshi57]

Amazing !! . Thanks a ton for your response . This is exactly what I need . Quick question - Shall I use the same formula for 15 min interval window as well , instead of 30 min interval ?

[DonkeyOte]

Yes the same formula should work with any time interval.

[vamshi57]

This formula worked for me fine before & now I have changed the timings and its not working . I have attached the spreadsheet where its giving me a zero value during certain intervals , which is not true .

[DonkeyOte]

Creating the end times by means of formula addition from start times (a change from original setup) the end "times" in C always exceed B thus the IF in the Array will never evaluate those shifts crossing midnight.

To illustrate... if you format C as General you will see a number of those entries > 1 (ie > 24 hours) and all values in C > B.

If you wish to keep C as they are you will need to modify the array (using MOD) so as to ensure only the time element is used from the possible datetime values, ie

Please Login or Register  to view this content.

[vamshi57]

Its working now . Thank you

[librandt]

DonkeyOte, I used your formula to calculate the total number of officers working at a given time, but have a few problems. 1st - i am ok @ excel, but have never worked with arrays! 2nd - because i can't figure out how to get the total scheduled for all three shifts at the top of the worksheet, i have calculated the # scheduled for each shift at the bottom of the page, and added them together to get a single total at the top. 3rd - can i use counta to count the total number of officers (columns c, h, o) instead of having to counta the single cells in that column over in f, l, r and add them together. 4th - i also need to be able to count the number of officer who call out, awol, etc. as will be coded in columns e, k, o - the codes will match the descriptions in row a (as shown in "pm"). I hate to ask for help, but i give up!

[jeffreybrown]

Hello librandt & Welcome to the Forum,

Administrative Note:

• Somebody would be happy to help with your query, but first, before we can proceed…
• Please see Forum Rule #2...Do not post a question in the thread of another member -- start your own thread..
• If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
• Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[librandt]

thanks - i posted in a new thread.