Forum Discussion
Subtract if true
I have a sheet with Covid vaccine bookings. It is for checking 2nd dose is more than or equal to 21 days from 1st dose
I have it showing the number of days from Dose 1 to Dose 2
Also have a TRUE of name in Colum E 1 is the same as E2 ........ We do have some 2 Dose only bookings
The spread sheet is sorted to name in Colum E then to Booking type Pfizer Dose 1 or 2. My date field is in Colum A Time is note needed. My True/False testing the Name field
The booking type is PfizerCovid1 or PfizerCovid2
Some people are only booked for PfizerCovid2 if they have had a vaccine some place other than my place.
The list is from 1 sep to 24 December and while I have can manually copy and past the =A3-A2 to see the what the number of days is I would love to do the whole spreadsheet at once and share it to other vaccine sites to help them as well.
In my fictitious list of names below Mast Rhys Chow would need to be changed as his 2nd is 16 days, Mr Ryan Dalmation who has a 27 day gap could be brought forward 6 days IF we have a spare dose.
My Nickname is Count Von Count as I have to count this several times a day at present.
Excuse me If I did not explain it correctly this morning.
| Date | Time | True/false | Num of days | Name | Booking type |
| 29/09/2021 | 9:15:00 | Mast Rhys Chow | PfizerCovid1 | ||
| 15/10/2021 | 12:45:00 | TRUE | 16 | Mast Rhys Chow | PfizerCovid2 |
| 25/09/2021 | 13:15:00 | FALSE | Ms Maria Thai | PfizerCovid1 | |
| 17/10/2021 | 11:15:00 | TRUE | 22 | Ms Maria Thai | PfizerCovid2 |
| 11/09/2021 | 8:15:00 | FALSE | Mr Ryan Dalmation | PfizerCovid1 | |
| 8/10/2021 | 9:15:00 | TRUE | 27 | Mr Ryan Dalmation | PfizerCovid2 |
| 18/09/2021 | 9:45:00 | FALSE | Mr Ryan Deep | PfizerCovid2 | |
| 18/09/2021 | 9:45:00 | FALSE | Mr Brian Deep | PfizerCovid2 | |
| 18/09/2021 | 9:45:00 | FALSE | Mrs Rose Deep | PfizerCovid2 | |
| 18/09/2021 | 9:45:00 | FALSE | Ms Paris Deep | PfizerCovid2 | |
| 30/09/2021 | 12:30:00 | FALSE | Mr Ryan Deer | PfizerCovid1 | |
| 21/10/2021 | 12:30:00 | TRUE | 21 | Mr Ryan Deer | PfizerCovid2 |
| 7/10/2021 | 12:00:00 | FALSE | Ms Sam Gail | PfizerCovid1 | |
| 29/10/2021 | 15:45:00 | TRUE | 22 | Ms Sam Gail | PfizerCovid2 |
| 23/09/2021 | 11:45:00 | FALSE | Mrs Stephanie Diver | PfizerCovid1 | |
| 14/10/2021 | 11:30:00 | TRUE | 21 | Mrs Stephanie Diver | PfizerCovid2 |
| 17/09/2021 | 13:45:00 | FALSE | Mr Stephen Speed | PfizerCovid1 | |
| 8/10/2021 | 13:45:00 | TRUE | 21 | Mr Stephen Speed | PfizerCovid2 |
| 25/09/2021 | 11:45:00 | FALSE | Mr Terry Big | PfizerCovid2 | |
| 25/09/2021 | 11:15:00 | FALSE | Mast Tom Wood | PfizerCovid1 | |
| 22/10/2021 | 10:00:00 | TRUE | 27 | Mast Tom Wood | PfizerCovid2 |
| 8/10/2021 | 14:00:00 | FALSE | Mast Tien Ji Chay | PfizerCovid1 | |
| 29/10/2021 | 14:45:00 | TRUE | 21 | Mast Tien Ji Chay | PfizerCovid2 |
=E1=E2 TRUE
=E2=E3 FALSE
Date
=A2-A1 is 21 days
=A6-A5 is 28
I have Tried =IF(E1+E2,A2-A1) and several like =IF(E1 ,A2-A1) which failed
Thanks for looking and any suggestions
4 Replies
can the list be modified in a more efficient format like this?
it basically subtracts the number of hours from midnight dose day1,
then calculates the number of days from dose day1, then adds the hours from midnight to proposed dose day 2 and
only 1 name (so no double booking mistakes).
- Not really. The 2nd dose only people do not need a booking. The only two things of interest is:
1- the number of days between people with a 1st and 2nd Dose
2- Anyone with a 1st dose and no 2nd Dose booked.
All the data comes out of a booking or appointment database with a date range set for several weeks in the future. It is thousands + of bookings which is why I would like to test it automatically
Unless I misunderstood your narrative:
if E1 is equal to E2
then E2 is not equal to E3
because there are only two values
either bright eyes OR not bright.
If the above interpretation of your narrative is correct then
the context of the formula is:
[IF TRUE CHECK DATE]
=IF(E1=E2,IF(A2-A1>=21, "Ok to book 2nd dose", "Less than 21 days"),"Not Bright")
Let me know if you have questions
cheers
- Edited as I was not clear. Thank you Yea. Keep safe and enjoy your formula's
