Counting days between appointments

Ask for Help
1

Hey Everyone!

I have a problem which I cannot really solve atm.
I’m making an appointment calendar for my business. I have different guests and provisions, but the days between two appointments is important (I calculate the price based on that).
So basically I need 2 columns in my table. The first should tell me the client’s previous (NOT LAST) appointment’s date, and the other should count the days between each other. I added a counter (manually) for each client’s appointments (like its her/his 1st, 2nd, 3rd time) and as long as I’m able to track it it would not be the problem. But its not updating automatically so i need a solution, to automatically tell the postition of the appointment among all client’s appointment based on the DATE of it.

Hope you can help. Thanks.

2

Can you clarify this please?
Do you mean:

  • The number of days between the previous appointment and the next appointment?
  • The number of days between the previous appointment and the current date?
  • Something else?
3

The number of days between two appointements. Current date is not relevant :slight_smile:
Other things I can handle easily. The main problem is, that when I open a form, and make a new appointment it should fill out some of the values automatically.

4

Can you tell us which fields should be filled automatically, related to your original question?

5

Okay, so I will assume that you have a table that lists all of your clients. In that table, create the following columns:

  • A Query column that targets your Appointments table, and configure as follows:
    – Filter where Client is This row->Client
    – Order by Appointment Date Descending
  • A Single Value column that takes the first Appointment Date from the Query column (this will be the date of each clients next appointment)
  • Single Value column that takes the “1 from start” Appointment Date from the Query column (this will be the date of each clients previous appointment)
  • Now you can use a Math column to calculate the number of days between the two dates using the following formula:
Round(Next - Previous)
6

Hey There! Thanks for the advice, it solved the problem Partly.
I had the same solution already (not as elegand as yours).
The only problem with it, that it’s only handle the last and 1 before.
I would need a solution which tells the difference between every appointment and the one before it.
The main reason for this, that one guest has 2-3 pendind appointment in the future all the time, and this only hadles the last and last-1.

7

Okay, I understand. Have a watch of the below video. I think you could adapt the technique shown to work for your use case.

https://www.loom.com/share/042be3be13004355b0dedec1be67b400

8

So basically the solution lies here. But needed a little twist.

Here is the twist:

For every appointment I added a query column:
-Where Client ID = Row Client ID
AND

  • The Start date is before the Start date of row
  • Ascending order

Single value column:
-Gets the first date from the Query

Math column:
Round (This - Previous)

Voilá :partying_face:
Million thanks for the help!