• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To check availability between two time ranges

K

Karthik Thandapani

Member
Hi,

I have a table which shows users are busy From & To. I have another table in which I need to see if a particular user is available at a particular time range. Presently I'm using Sumproduct--( function to arrive at 'how many times the user is repeating with the same time range. But bit struggling with how to find availability between two ranges.
Can anybody help. Please see the attached file.

Thanks,
Karthik
 

Attachments

Hi Karthik ,

You need to explain your requirement in more detail.

Given the data in your file , suppose I select IED and a From Time of 7:30 in I3 , and a To Time of 7:50 in J3 ; what should be the result ?

Narayan
 
Hi Faseeh,

Please note, the formula doesn't pull the count inbetween the time range(B3:B7>=I3)*(C3:C7<=J3). I have attached the sheet with both the formulae. Pls check.

Regards,
Karthik
 

Attachments

Hi Narayan,

Hope you're doing great there. I just need the count of occurrences using Sumproduct. The problem is how to identify user with time range within the busy times. Presently it shows the exact time only. Pls try. From there I'm conditional formatting it.
 
Hi Karthik ,

I think you have not read my post fully ; I gave a specific case , and wanted to know the result ; unless I can understand the logic of your requirement , at least I cannot just throw out a formula.

Narayan
 
@Karthik Thandapani

I think you need to elaborate all the requirements as said by @NARAYANK991 , there will be issues in cases as you have mentioned in your last post, what to do if one of the ending and starting time is within the range of time you are looking for is the question needing answer.
 
@NARAYANK991 , @Faseeh , Sure Let me provide more details on that. The requirement is to check if a particular user is available for a activity. For an example: User: IED is already busy from 7:40 AM to 11:00 AM. I have a new activity that starts at 10:00 AM & ends at 10:30 AM. I need to find if user IED is available for the new activity.
The formula should find if IED is available for new activity that starts at 10:00 AM & ends at 10:30 AM or busy?
Also, if I have another activityII time 8:00 AM to 10 AM, this falls between the time range of IED which is already busy.
Are we clear or do you need more info?
 

Attachments

...OK and what if you have an activity that starts 7:00 AM and ends 10:30 AM or starts at 10:00AM and ends at 11:30 AM? In first case, IED was available for 40 min until he started at 7:40 and for the second on was busy till 11:00 AM and activity ended at 11:30 AM (he had spare 30 min) how you are going to handle it.
 
Hi Karthik ,

Let me put in some more detail :

1. You have a table listing each user name , and the From_Time and To_Time when they are busy and therefore Not Available.

2. Suppose you input a user name , and a particular Start_Time and End_Time.

3. If Start_Time is greater than To_Time , or if End_Time is less than From_Time , then there is no overlap , and the user is available ; is this correct ?

For instance , a user X is busy from 8:00 AM till 1:45 PM

Suppose the Start_Time is 7:30 AM and the End_Time is 10:30 AM , then the output will be that the user is Not Available. For the user to be available , the Start_Time and End_Time have to be before 8:00 AM or after 1:45 PM. Correct ?

Narayan
 
Hi Karthik ,

In that case , see if this works :

=SUMPRODUCT((A3:A7=G3)*((B3:B7>=J3)+(C3:C7<=I3)))

I have assumed that I3 is less than J3.

Narayan
 
You must log in or register to reply here.
Back
Top