How can I modify this equation?

Pauline Moyes

I am running a report based off a request intake sheet that pulls the 1st choice date and 2nd choice date selected on a form request. This report will be put onto a dashboard and show only available dates (dates that have not been selected as either a first choice or second choice date on a form submission). I have created the following formula to mark the status as either overbooked, booked, or available.

=IF((COUNTIF({Date 1}, Date@row) + COUNTIF({Date 2}, Date@row)) > 1, "Overbooked", IF((COUNTIF({Date 1}, Date@row) + COUNTIF({Date 2}, Date@row)) > 0, "Booked", "Available"))

Then, I have used the report to only show 'Available'. SO, when a new form is submitted, both the first and second choice dates no longer show up in the report.

How can I modify this formula to only mark first choice dates as booked, and second choice dates remain as 'Available' along with dates that have not yet been selected? In other words, if someone selects 10/21 as a first choice date and 10/22 as a second choice date, I only want the first choice date to be marked as 'Booked', and the second choice date to remain as 'Available.'

I hope this makes sense!

Genevieve P.

Hi @Pauline Moyes

If I'm understanding your system correctly, you would need an indicator in the intake sheet that marks when a date has been booked from the first choice, so that your formula can tell when to move to the second choice, instead.

You would also need to tell it to ignore the second choice if the first one was OK and could be used, correct? So if everyone put Tuesday as their second choice, but their first choices are all free, the Tuesday would still be available.

In this case, one way to do this would be to set up a hidden, helper checkbox column that checks to see if this is the second time a First Choice has been selected, and if it is, checks the box. This checkbox formula would be very similar to your current one:

=IF(COUNTIF([First Choice]:[First Choice], [First Choice]@row) > 1, 1, 0)

Now, I've also added another element to this. I only want the box to be checked if it's the SECOND time that this date has been chosen... whereas right now it will check for both the first and second times, as soon as it sees a duplicate.

To ignore the first submission, you can add one of the System Columns to your sheet - the Created Date column which indicates when a submission has come in or a new row was created. We can then use MIN(COLLECT to search for the oldest created date for that First Choice entry:

CHECKBOX FORMULA:

=IF(Created@row = MIN(COLLECT(Created:Created, [First Choice]:[First Choice], [First Choice]@row)), 0, IF(COUNTIF([First Choice]:[First Choice], [First Choice]@row) > 1, 1, 0))

Note: This presumes your date columns are titled First Choice and the helper Created date is called Created.

Then you can use this helper column in your cross-sheet reference formula to search the Second Choice, but only if the box is checked.

I'll break down the formula first, then have the full statement at the bottom.

OVERBOOKED Rule:

There are 3 possible ways that a date could be "Overbooked":

1. Either this has been chosen as a First Date twice,
2. or it has been chosen as a Second Date more than once where a First Date is booked
3. or it has been chosen as a First Date but it's already someone's Second Date (because their First Date was booked)

=IF(OR(

1 . COUNTIFS({Helper Checkbox}, 1, {Date 1}, Date@row) >= 1,

2 . COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) > 1,

3 . AND(COUNTIFS({Helper Checkbox}, 0, {Date 1}, Date@row) = 1, COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) >= 1)), "Overbooked"

BOOKED Rule:

There are 2 ways a date can be simply "Booked":

1. Either it has been chosen once for a First Date
2. or it has been chosen as a Second Date on a row where the First Date is already booked

IF(OR(

1 . COUNTIFS({Helper Checkbox}, 0, {Date 1}, Date@row) = 1,

2 . COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) = 1), "Booked",

FULL FORMULA:

=IF(OR(COUNTIFS({Helper Checkbox}, 1, {Date 1}, Date@row) >= 1, COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) > 1, AND(COUNTIFS({Helper Checkbox}, 0, {Date 1}, Date@row) = 1, COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) >= 1)), "Overbooked", IF(OR(COUNTIFS({Helper Checkbox}, 0, {Date 1}, Date@row) = 1, COUNTIFS({Helper Checkbox}, 1, {Date 2}, Date@row) = 1), "Booked", "Available"))

Let me know if this works for you, and if I've understood what you're looking to accomplish!

Cheers,

Genevieve

Pauline Moyes

Hi Genevieve,

Thank you so much for your help! I guess I do not have an issue with the formula that categorizes the selections as "overbooked," "booked" or "available, but more so with the report formula that allows the report to still show both booked and available dates. I am not sure if I am making sense. :)

Genevieve P.

Hi @Pauline Moyes

Reports will filter rows based on specific criteria that you set in the report Builder... is that what you're looking to adjust? It would be helpful to see a screen capture of your current Sheet, Report, and Report Builder showing the criteria to understand what you'd like to see instead (but please block out any sensitive data in these images!)

Thanks,

Genevieve