Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating calendar days excluding holidays Mickey Excel Programming 3 October 25th 08 05:32 PM
Conditional Formatting Dates calculating 10 days and 30 days from a certain date Sioux[_2_] Excel Worksheet Functions 2 October 11th 07 02:04 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Calculating # of busn days, not just calendar days, between 2 dates? StargateFanFromWork Excel Programming 1 June 29th 04 08:31 PM
Number of days between dates (Calendar) Malcolm Excel Programming 1 September 9th 03 12:43 PM


All times are GMT +1. The time now is 04:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"