Forum Discussion
Finding an appointment in Excel
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!
Deleted
Here is a first attempt. It may need additional tweaking.
Do not change the formulas in column D.
14 Replies
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:
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!
Deleted
See the attached version. I used a helper column; it is undoubtedly possible without that column too but it'd be more complicated.