Subtract if true

New Contributor

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. 

 

 

 

 

DateTimeTrue/falseNum of daysNameBooking type
29/09/20219:15:00  Mast Rhys ChowPfizerCovid1
15/10/202112:45:00TRUE16Mast Rhys ChowPfizerCovid2
25/09/202113:15:00FALSE Ms Maria ThaiPfizerCovid1
17/10/202111:15:00TRUE22Ms Maria ThaiPfizerCovid2
11/09/20218:15:00FALSE Mr Ryan DalmationPfizerCovid1
8/10/20219:15:00TRUE27Mr Ryan DalmationPfizerCovid2
18/09/20219:45:00FALSE Mr Ryan DeepPfizerCovid2
18/09/20219:45:00FALSE Mr Brian DeepPfizerCovid2
18/09/20219:45:00FALSE Mrs Rose DeepPfizerCovid2
18/09/20219:45:00FALSE Ms Paris DeepPfizerCovid2
30/09/202112:30:00FALSE Mr Ryan DeerPfizerCovid1
21/10/202112:30:00TRUE21Mr Ryan DeerPfizerCovid2
7/10/202112:00:00FALSE Ms Sam GailPfizerCovid1
29/10/202115:45:00TRUE22Ms Sam GailPfizerCovid2
23/09/202111:45:00FALSE Mrs Stephanie DiverPfizerCovid1
14/10/202111:30:00TRUE21Mrs Stephanie DiverPfizerCovid2
17/09/202113:45:00FALSE Mr Stephen SpeedPfizerCovid1
8/10/202113:45:00TRUE21Mr Stephen SpeedPfizerCovid2
25/09/202111:45:00FALSE Mr Terry  BigPfizerCovid2
25/09/202111:15:00FALSE Mast Tom WoodPfizerCovid1
22/10/202110:00:00TRUE27Mast Tom WoodPfizerCovid2
8/10/202114:00:00FALSE Mast Tien Ji ChayPfizerCovid1
29/10/202114:45:00TRUE21Mast Tien Ji ChayPfizerCovid2

 

=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

@NavyDiver 

 

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

@NavyDiver 

 

can the list be modified in a more efficient format like this?

Yea_So_0-1632932899668.png

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