Calculating calendar days and completion dates.

[primed]

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

 

[OssieMac]

Have a look in Help for the worksheet function Workday. I feel sure that it
will help you to do what you want.

As this is an Excel programming site, then you possibly want to use it with
VBA. if so, then the code is as follows.
WorksheetFunction.WorkDay(start_date,days,holidays)

Could also look at NETWORKDAYS function. use it similarly to above in VBA.

 

[primed]

Hi OssieMac,

How do i change the workday function to a 6 day week?

 

[OssieMac]

How do i change the workday function to a 6 day week?

I don't think you can. I didn't realize that you were working with a 6 day
week when I replied.

 

[primed]

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?