Finding an appointment in Excel

[Deleted]

Hi there, I got a question for you. Imagine, in columns B and C I have the dates and times of my appointments. As you can see, I have two appointments per Sunday. Now, how can I in column G create a list with only those Sundays where I can have an appointment at 10:00 hours where I have at least 5 (see E2) hours to the next appointment? In this particular case, this should only be 28.04.2024. Thank you in advance!

[#2]

@Deleted 

=LET(dates, UNIQUE(B2:B9), earliest, MINIFS(C2:C9, B2:B9, dates), FILTER(dates, earliest>=TIME(10, 0, 0)+E2))

Format the spill range of this formula as a date.

With slightly different data:

[#3]

@HansVogelaar

Thank you very much, my landgenoot. This is already a great help! I've edited the file a bit. In E2 I now have the time, that I want to find an appointment for and in E3 I now have the minimum hours between the starts of appointments. The first question I have is, how can I change the formula in G2 so that when I enter e.g. 21:00 in E2, 28.04.2024 pops up in G2? The second question I have is, how can I change the formula in G2 so that when there are no results it says e.g. "No results" (instead of "#CALC!"). And the third question I have is, as you can I see I have added Sundays in May when I don't have appointments yet. How can I change the formula in G2 so that these open Sundays are included, as well? Thank you very much in advance!

[#4]

@Deleted 

See the attached version. I used a helper column; it is undoubtedly possible without that column too but it'd be more complicated.

[#5]

Thanks again!
If I have 09:00 in D2, 10:00 in D3, and 17:00 in E2, it says "No results" in G2. However, as both 09:00 (D2) and 10:00 (D3) have 05:00 (E3) or more hours difference with 17:00 (E2), an appointment should be possible on this date.
A second question I have, how can I edit the formula in G2 so that it only lists dates within 365 days of today?
By the way, in D10:D13 I have entered 03:00 instead, so that appointments between 08:00 and 22:00 should be possible (when E3 remains 05:00).
Thank you in advance!

[#6]

@Deleted

• You originally asked to determine whether there was enough time before the earliest appointment on a day for a new appointment. The desired appointment time was before the existing appointments.
• Now you specify a desired appointment time after the existing appointments. So you apparently want something else. Please clarify.

[#7]

Please do accept my apologies for the confusion. I have various appointments on a particular weekday. Sometimes one, other times more. Clients ask for an appointment at a certain time. Then I’d like to fill out this time in E2 and then get a list in the G column on which dates I still have room for this appointment with at least 5 (or whatever I’ve filled out in E3) hours between the start time of appointments. So, if I have an appointment at 14:00, the appointment before cannot start later than 09:00 and the one after cannot start earlier than 19:00. In the columns B and C, I fill out the dates and times I already have appointments. However, when a weekday is not listed there, that means I’m available, so these dates should also appear in the G column. The list in the G column should only propose dates within 365 days of today. Thanks again for your help. It is much appreciated.

[#8]

@Deleted 

Is 22:00 the latest time an appointment can end, or is it the latest time an appointment can begin?

[#9]

With having 02:00 o’clock for dates that I don’t have an appointment for yet (C10:C13) and with having 05:00 (E3) hours between the start times of appointments, start times of appointments are between 07:00 and 21:00 o’clock. So, 21:00 o’clock is the latest time an appointment can begin.

[#10]

@Deleted 

I'm confused.

In your sample workbook, you have appointments on the same day at for example 15:00 and 16:00, and yet you state that you want 5 hours between the start of appointments. How does that work?

[#11]

You're right. The times in the C column are only test times to see how the G column responds.

[#12]

@Deleted

Let's say you have appointments on 7:00 and on 17:00 on the same day, and that the requested time is 12:00. Do you want to include this date too?

[#13]

Exactly! (As there are 5 hours or more between 07:00 and 12:00 and between 12:00 and 17:00.)

[#14 · Accepted solution]

@Deleted 

Here is a first attempt. It may need additional tweaking.

Do not change the formulas in column D.

[#15]

This is just awesome, thank you so much for your effort!