Inserting rows chronologically while maintaining weekday formula

Closed
Report
Posts
1
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 6, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello there,

In a sheet named Projects, col C contains weekday dates and col B has numbers of days needed for projects (a project takes at least 1 day). Dates in col C, such as in C12, are calculated as =WORKDAY(C11,B11). Likewise, the date in C13 is =WORKDAY(C12,B12). Col headings are on row 10. If a project lasted for more than 1 day, then the weekdays will advance depending on the number of days entered for a project---this works good.

However, this needs to be altered to check against dates in another sheet, called Appointments. Dates in the Appointments sheet should interfere with dates in the Projects sheet. The Appointments sheet is laid like the Projects sheet, with dates in col C. If there is a date in the Appointments sheet that is the same as a date in the Projects sheet (or happens to fall on a day of a multiple-day project), then insert the date's row of the Appointments sheet into the chronologically correct row in the Projects sheet.

The hard part is figuring out how to split a multiple-day project into multiple rows. If this happens, the interfering part of the multiple-day project needs to be advanced to the next workday after the Appointment's date. I'm having a difficult time even starting to think how best to do this. Your help is appreciated.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you post a sample book at some public file share site and post back link to ACTUAL FILES back here to have a better understanding
0