Spreadsheet: 811056d1672249782-date-formulae-not-producing-correct-dates-dates-not-linking-together
Sheet: INPUT SHEET

 |  |  |  |  |  |  |  |  |  |  |  |  | HOLIDAY | Date
 |  | Required | Fitted |  |  |  |  |  |  |  |  |  | [NAME] | 2023-04-20 00:00:00
 |  | 8 |  |  |  |  |  |  |  |  |  |  | [NAME] | 2023-04-21 00:00:00
 |  | Required entered manually | Total fitted to date is sum of Sets in Col E |  |  |  |  |  |  |  |  |  | [NAME] | 2023-04-22 00:00:00 |  |  | Simple | New
 |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] | 2023-04-23 00:00:00 |  |  | Smart | Existing
 |  |  | Aligners |  |  |  | Dentist Approves |  | Appointment date |  |  |  | [NAME] Day | 2023-06-27 00:00:00 |  |  |  |  |  |  |  |  |  |  | 2. Next Ref number in sequence will be created automatically.
 |  | Action | Ordered | Sets | Due | Actual | Planned | Actual | Planned | Rescheduled |  |  | Eid al Adha | 2023-06-28 00:00:00 |  |  |  |  |  |  |  |  |  |  | 3. Enter Name in E3.
 |  | First Appointment | 2023-01-02 00:00:00 | 2 |  |  |  |  | 2023-03-01 00:00:00 | 2023-03-06 00:00:00 |  |  | Eid al Adha | 2023-06-29 00:00:00 |  |  |  |  |  | Actual | APPOINTMENTS | Date | Sets |  | 4. Select Treatment Path from DropList in F3
 |  |  | 2023-03-20 00:00:00 | 2 |  |  |  |  |  |  |  |  | Eid al Adha | 2023-06-30 00:00:00 |  |  |  |  |  |  |  |  | 2 |  | If Treatment Path blank, Dentist approval fills in red, and subsequent Planned dates are blank.
 |  |  |  |  |  |  |  |  |  |  |  |  | Islamic New Year | 2023-07-19 00:00:00 |  |  |  |  |  |  |  |  |  |  | 5. Enter [NAME] and [NAME] payment values
 |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] | 2023-09-27 00:00:00 |  |  |  |  |  |  |  |  |  |  | Any Dates on a Sunday are reset to following Monday automatically.
 |  |  |  |  |  |  |  |  |  |  |  |  | Commemoration Day | 2023-12-01 00:00:00 |  |  |  |  |  |  |  |  |  |  | 11 Enter date of First appointment and no. of sets you will issue then.
 |  | 1. [NAME] entered manually. |  |  |  |  |  |  |  |  |  |  | National Day | 2023-12-02 00:00:00 |  |  |  |  |  |  |  |  |  |  | 11. If the total issued is less than the Full Amount, next Appointment date is set automatically, based on number of sets.
 |  | 2. No of Sets entered Manually |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | After Print Files received, |  |  |  | (E.g if you issue three sets, the next appointment is (3 x 14 days). If you issue five, it will be (5 x 14)= 90 days.
 |  | 3. First Due date should be either Rescheduled Date -4 days, or Planned Date (Col J) - 4days.  BUT if that makes it a Monday or Public Holiday, it should be the previous working day (Not Monday or Holiday). |  |  | 4. [NAME] dates should be one week before the Rescheduled or Planned Appointment Date, UNLESS that makes it a Monday or Public Holiday, when it should be reset to the previous working day. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Dentist requests Aligners |  |  |  | If you enter more than the Full amount, cells fill in red. Reduce the sets to be issued at the appointment until they equal the Full Amount.
 |  | =IF(E8="","",IF(AND(K8<>"",WEEKDAY(K8-4)=1),K8-5,IF(AND(K8<>"",WEEKDAY(K8-4)<>1),K8-4,IF(AND(J8<>"",WEEKDAY(J8-4)=1),J8-5,IF(AND(J8<>"",WEEKDAY(J8-4)<>1),J8-4))))) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Aligners delivered |  |  |  | 12. [NAME]. Record created on next free row of database.
 |  | (a) Needs to include Public Holidays (b) Not working if there is no rescheduled Date in Col K |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Dentist approves - 1 day |  |  |  | 13. New Patient name and Ref add automatically to lists on RANGES worksheet.
 |  | 5. Actual Date entered manually |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | First appointment |  |  |  | 14. Press CLEAR button to reseet form.
 |  | 6. Approval date should be two days after either the "Actual" or "Due" date BUT BUT if that makes it a Monday or Public Holiday, it should be the NEXT working day (not Monday or Holiday) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Aligners due |  |  |  | Existing Patient.
 |  | =IF(AND(G8<>"",WEEKDAY(G8+2)=1),G8+3,IF(AND(G8<>"",WEEKDAY(G8+2)<>1),G8+2,IF(AND(F8<>"",WEEKDAY(F8+2)=1),F8+3,IF(AND(F8<>"",WEEKDAY(F8+2)<>1),F8+2,"")))) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Dentist requerst next sets - 1 week before next appointment |  |  |  | 1. Select "Existing from Status DropList.
 |  | (a) Needs to include Public Holidays (b) Not working if there is no rescheduled Date in Col K, because Col F is "#[NAME]!' |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2. Select Ref number from Droplist.
 |  | 7. Actual Date entered manually |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3. All other data will be exracted from current record on database.
 |  | 8. FIRST Appointment Date set [NAME]. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4. Change or add whatever data you want.
 |  | 9. Following appointment Actual Date adds two weeks for every Seet issued in PREVIOUS appointment). |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5. Click UPDATE RECORD. Changes will be "overwritten" on existing record on Database.
 |  | =IF(OR(E9="",SUM(E$8:E9)>C$4),"",IF(AND(K8<>"",WEEKDAY(K8+(E8*14))=1),K8+(E8*14)+1,IF(AND(K8<>"",WEEKDAY(K8+(E8*14))<>1),K8+(E8*14),IF(AND(J8<>"",WEEKDAY(J8+(E8*14))=1),J8+(E8*14)+1,IF(AND(J8<>"",WEEKDAY(J8+(E8*14))<>1),J8+(E8*14)))))) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6. Press CLEAR button to reseet form.
 |  | 10. If appointment rescheduled, date entered [NAME], cell(s) fill in red.