Forum Discussion
Finding an appointment in Excel
Deleted
Here is a first attempt. It may need additional tweaking.
Do not change the formulas in column D.
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.
- 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!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.
- 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.