Forum Discussion

MikeTouche's avatar
MikeTouche
Copper Contributor
Jul 25, 2023

Tracking Appointments

I am trying to create a table for tracking client appointments where cell A shows the date the client was seen and cell B shows the next appointment which is the following week on the same day of the week(so 7 days away). I know to use the fill series option, but I am wondering if there is a way to have cell A auto fill to the date in cell B once it becomes that date and then have cell B auto fill with new date 7 days out from date that is now in cell A.

Charting
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 26, 2023

    MikeTouche 

    To achieve the auto-fill functionality you described in Excel 365, you can use a combination of formulas and conditional formatting.

    Here is how you can set it up:

    1. Enter the first date in cell A. Let us say cell A2 has the initial date.
    2. In cell B2, use the following formula to calculate the next appointment date (7 days later):

    =A2 + 7

    1. Now, select cells A2 and B2 together and drag the fill handle (the small square in the bottom-right corner of the selection) down to fill the formula in column B. This will automatically populate the next appointment dates in column B based on the dates in column A.
    2. For the conditional formatting part, select cells A2:B2 (the first row with dates) and go to the "Home" tab on the Excel ribbon.
    3. Click on "Conditional Formatting" and then choose "New Rule."
    4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
    5. In the "Format values where this formula is true" box, enter the following formula:

    =A2=TODAY()

    1. Click on the "Format" button and choose the formatting you want to apply when the appointment date matches the current date. For example, you can set the font color or background color to highlight the current date.
    2. Click "OK" to close the "Format Cells" dialog box.
    3. Click "OK" again to close the "New Formatting Rule" dialog box.

    Now, when you open the workbook on any given day, cell A will automatically show the current date (highlighted with the conditional formatting), and cell B will show the next appointment date (which will change automatically as days pass).

    Please note that the date in cell A will not automatically update unless you recalculate the workbook (e.g., by pressing F9). If you want the dates to update automatically without the need for manual recalculation, you may need to use VBA macros. However, for most scenarios, the above steps should provide a simple and effective way to track client appointments. The text and the steps are the result of various AI's put together.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources