Sep 28 2021 05:33 PM - edited Sep 29 2021 12:39 AM
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
Sep 28 2021 10:12 PM
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
Sep 29 2021 12:40 AM
Sep 29 2021 09:28 AM - edited Sep 29 2021 09:36 AM
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).
Sep 29 2021 10:39 PM