My fault, at least i learnt a couple more functions.

I think we can do it by using my calander spreadsheet combined with some code.
To calculate column "P" on the EOT Register the code should do the following:
1) Return the cell reference where calander!A2:1872 = Column P
2) count the rows on the calendar sheet starting from the cell reference at
item (1) until the number of work days (EOT Register!column I) is reached.
Rows must not be counted if there is a "1" in Column C or D on the Calendar
sheet.
3)Once the work days (EOT!columnI) is reached the date in Calendar!column A
is our Answer.

Does that make sense?


"primed" wrote:

Hi,

I need help converting working days to calendar days, to give me a
completion date.

I have a calendar (sheet name "Calendar") with the following columns
Column A - Date
Column B - Day of week (ie 1 to 7, 1 being a Sunday).
Column C - Non Working Days (contains a 1 if the row is a Sunday)
Column D - Holidays (contains a 1 if the row is a public holiday)
Headings are row 1, Data is row 2 to Row 1872

My second sheet is called "EOT Register". This contains a summary of how the
completion date was calculated.

Column F contains the start date(f2:f72)
Column I contains the number of work days which needs to be added.
Column K should total the number of Non working days ie Sundays between the
start date and completion date
Column M should total the number of holidays between the start date and
completion date
column N is where the calendar days needs to be calculated
Column P is where the completion date needs to be calculated(Col F+ Col N)

Columns K,M,N,P are the columns that i need help with.

Regards
Primed