Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating calendar days and completion dates.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating calendar days and completion dates.
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. -- Regards, OssieMac "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating calendar days and completion dates.
Hi OssieMac,
How do i change the workday function to a 6 day week? "OssieMac" wrote: 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. -- Regards, OssieMac "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating calendar days and completion dates.
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. -- Regards, OssieMac "primed" wrote: Hi OssieMac, How do i change the workday function to a 6 day week? "OssieMac" wrote: 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. -- Regards, OssieMac "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating calendar days and completion dates.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating calendar days excluding holidays | Excel Programming | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Calculating # of busn days, not just calendar days, between 2 dates? | Excel Programming | |||
Number of days between dates (Calendar) | Excel Programming |