Discussion Re: Subtract if true in Excel
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2793998#M115823
<P><LI-USER uid="1169449"></LI-USER> </P><P> </P><P>Unless I misunderstood your narrative:</P><P>if E1 is equal to E2</P><P>then E2 is not equal to E3</P><P>because there are only two values</P><P>either bright eyes OR not bright.</P><P>If the above interpretation of your narrative is correct then</P><P>the context of the formula is:</P><P> [IF TRUE CHECK DATE]</P><P> </P><PRE>=IF(E1=E2,IF(A2-A1>=21, "Ok to book 2nd dose", "Less than 21 days"),"Not Bright")</PRE><P>Let me know if you have questions </P><P> </P><P>cheers</P>Wed, 29 Sep 2021 05:12:41 GMTYea_So2021-09-29T05:12:41ZSubtract if true
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2793663#M115812
<P>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</P><P>I have it showing the number of days from Dose 1 to Dose 2 </P><P>Also have a TRUE of name in Colum E 1 is the same as E2 ........ We do have some 2 Dose only bookings</P><P> </P><P>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 </P><P>The booking type is PfizerCovid1 or PfizerCovid2</P><P>Some people are only booked for PfizerCovid2 if they have had a vaccine some place other than my place. </P><P> </P><P>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 <U><STRONG>and share it to other vaccine sites to help them as well. </STRONG></U></P><P>In my fictitious list of names below Mast Rhys Chow would need to be changed as his 2nd is 16 days, <SPAN>Mr Ryan Dalmation who has a 27 day gap could be brought forward 6 days IF we have a spare dose. </SPAN></P><P><SPAN>My Nickname is Count Von Count as I have to count this several times a day at present. </SPAN></P><P><SPAN>Excuse me If I did not explain it correctly this morning. </SPAN></P><P> </P><P> </P><P> </P><P> </P><TABLE width="501"><TBODY><TR><TD width="75">Date</TD><TD width="64">Time</TD><TD width="64">True/false</TD><TD width="96">Num of days</TD><TD width="109">Name</TD><TD width="93">Booking type</TD></TR><TR><TD>29/09/2021</TD><TD>9:15:00</TD><TD> </TD><TD> </TD><TD>Mast Rhys Chow</TD><TD>PfizerCovid1</TD></TR><TR><TD>15/10/2021</TD><TD>12:45:00</TD><TD>TRUE</TD><TD>16</TD><TD>Mast Rhys Chow</TD><TD>PfizerCovid2</TD></TR><TR><TD>25/09/2021</TD><TD>13:15:00</TD><TD>FALSE</TD><TD> </TD><TD>Ms Maria Thai</TD><TD>PfizerCovid1</TD></TR><TR><TD>17/10/2021</TD><TD>11:15:00</TD><TD>TRUE</TD><TD>22</TD><TD>Ms Maria Thai</TD><TD>PfizerCovid2</TD></TR><TR><TD>11/09/2021</TD><TD>8:15:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Ryan Dalmation</TD><TD>PfizerCovid1</TD></TR><TR><TD>8/10/2021</TD><TD>9:15:00</TD><TD>TRUE</TD><TD>27</TD><TD>Mr Ryan Dalmation</TD><TD>PfizerCovid2</TD></TR><TR><TD>18/09/2021</TD><TD>9:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Ryan Deep</TD><TD>PfizerCovid2</TD></TR><TR><TD>18/09/2021</TD><TD>9:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Brian Deep</TD><TD>PfizerCovid2</TD></TR><TR><TD>18/09/2021</TD><TD>9:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mrs Rose Deep</TD><TD>PfizerCovid2</TD></TR><TR><TD>18/09/2021</TD><TD>9:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Ms Paris Deep</TD><TD>PfizerCovid2</TD></TR><TR><TD>30/09/2021</TD><TD>12:30:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Ryan Deer</TD><TD>PfizerCovid1</TD></TR><TR><TD>21/10/2021</TD><TD>12:30:00</TD><TD>TRUE</TD><TD>21</TD><TD>Mr Ryan Deer</TD><TD>PfizerCovid2</TD></TR><TR><TD>7/10/2021</TD><TD>12:00:00</TD><TD>FALSE</TD><TD> </TD><TD>Ms Sam Gail</TD><TD>PfizerCovid1</TD></TR><TR><TD>29/10/2021</TD><TD>15:45:00</TD><TD>TRUE</TD><TD>22</TD><TD>Ms Sam Gail</TD><TD>PfizerCovid2</TD></TR><TR><TD>23/09/2021</TD><TD>11:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mrs Stephanie Diver</TD><TD>PfizerCovid1</TD></TR><TR><TD>14/10/2021</TD><TD>11:30:00</TD><TD>TRUE</TD><TD>21</TD><TD>Mrs Stephanie Diver</TD><TD>PfizerCovid2</TD></TR><TR><TD>17/09/2021</TD><TD>13:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Stephen Speed</TD><TD>PfizerCovid1</TD></TR><TR><TD>8/10/2021</TD><TD>13:45:00</TD><TD>TRUE</TD><TD>21</TD><TD>Mr Stephen Speed</TD><TD>PfizerCovid2</TD></TR><TR><TD>25/09/2021</TD><TD>11:45:00</TD><TD>FALSE</TD><TD> </TD><TD>Mr Terry Big</TD><TD>PfizerCovid2</TD></TR><TR><TD>25/09/2021</TD><TD>11:15:00</TD><TD>FALSE</TD><TD> </TD><TD>Mast Tom Wood</TD><TD>PfizerCovid1</TD></TR><TR><TD>22/10/2021</TD><TD>10:00:00</TD><TD>TRUE</TD><TD>27</TD><TD>Mast Tom Wood</TD><TD>PfizerCovid2</TD></TR><TR><TD>8/10/2021</TD><TD>14:00:00</TD><TD>FALSE</TD><TD> </TD><TD>Mast Tien Ji Chay</TD><TD>PfizerCovid1</TD></TR><TR><TD>29/10/2021</TD><TD>14:45:00</TD><TD>TRUE</TD><TD>21</TD><TD>Mast Tien Ji Chay</TD><TD>PfizerCovid2</TD></TR></TBODY></TABLE><P> </P><P>=E1=E2 TRUE</P><P>=E2=E3 FALSE</P><P>Date </P><P>=A2-A1 is 21 days </P><P>=A6-A5 is 28 </P><P>I have Tried =IF(E1+E2,A2-A1) and several like =IF(E1 ,A2-A1) which failed</P><P> </P><P>Thanks for looking and any suggestions</P><P> </P><P> </P>Wed, 29 Sep 2021 07:39:01 GMThttps://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2793663#M115812NavyDiver2021-09-29T07:39:01ZRe: Subtract if true
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2793998#M115823
<P><LI-USER uid="1169449"></LI-USER> </P><P> </P><P>Unless I misunderstood your narrative:</P><P>if E1 is equal to E2</P><P>then E2 is not equal to E3</P><P>because there are only two values</P><P>either bright eyes OR not bright.</P><P>If the above interpretation of your narrative is correct then</P><P>the context of the formula is:</P><P> [IF TRUE CHECK DATE]</P><P> </P><PRE>=IF(E1=E2,IF(A2-A1>=21, "Ok to book 2nd dose", "Less than 21 days"),"Not Bright")</PRE><P>Let me know if you have questions </P><P> </P><P>cheers</P>Wed, 29 Sep 2021 05:12:41 GMThttps://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2793998#M115823Yea_So2021-09-29T05:12:41ZRe: Subtract if true
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2794312#M115837
Edited as I was not clear. Thank you Yea. Keep safe and enjoy your formula'sWed, 29 Sep 2021 07:40:28 GMThttps://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2794312#M115837NavyDiver2021-09-29T07:40:28ZRe: Subtract if true
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2795889#M115896
<P><LI-USER uid="1169449"></LI-USER> </P><P> </P><P>can the list be modified in a more efficient format like this?</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Yea_So_0-1632932899668.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/313631i36B19E7DB600DD9A/image-size/medium?v=v2&px=400" role="button" title="Yea_So_0-1632932899668.png" alt="Yea_So_0-1632932899668.png" /></span></P><P>it basically subtracts the number of hours from midnight dose day1,</P><P>then calculates the number of days from dose day1, then adds the hours from midnight to proposed dose day 2 and</P><P>only 1 name (so no double booking mistakes).</P><P> </P><P> </P>Wed, 29 Sep 2021 16:36:45 GMThttps://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2795889#M115896Yea_So2021-09-29T16:36:45ZRe: Subtract if true
https://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2797841#M115979
Not really. The 2nd dose only people do not need a booking. The only two things of interest is:<BR />1- the number of days between people with a 1st and 2nd Dose<BR />2- Anyone with a 1st dose and no 2nd Dose booked.<BR />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 automaticallyThu, 30 Sep 2021 05:39:01 GMThttps://techcommunity.microsoft.com/t5/excel/subtract-if-true/m-p/2797841#M115979NavyDiver2021-09-30T05:39:01Z