Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get "EndDate" of a project?
Hi all,
I am wondering how can I get the end date of a project if i have the starting date, the workdays that it will need, the fact that a week have 5 working days and a list of hollydays? the problem is that in B:B i have many projects each starting in a different date, some in 2008 some in 2009, i imported to C:C the working days. Now in D:D and i have the hollydays in format "dd-mm" independent of the year, so now what should i do to get the end day? including the weekends and if any date from the hollydays, happen to be after the starting date of the project and automatically get the end date. which will therefore be, StartDay+HollyDays+WeekEnd +WorkDays, all these add up together to the EndDay. sample data: Start date Work days 05.21.2008 9 11.05.2008 124 12.28.2008 35 02.07.2009 114 and the hollydays are in the form DD - MM (year - independant) DD MM 12 01 11 02 3 03 25 04 22 07 1 09 2 11 21 11 31 12 How should i approach such a problem? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get "EndDate" of a project?
See Help for the WORKDAY() function.
For holidays, I would combine them into one cell per holiday, rather than monht/day. In article , Totti wrote: Hi all, I am wondering how can I get the end date of a project if i have the starting date, the workdays that it will need, the fact that a week have 5 working days and a list of hollydays? the problem is that in B:B i have many projects each starting in a different date, some in 2008 some in 2009, i imported to C:C the working days. Now in D:D and i have the hollydays in format "dd-mm" independent of the year, so now what should i do to get the end day? including the weekends and if any date from the hollydays, happen to be after the starting date of the project and automatically get the end date. which will therefore be, StartDay+HollyDays+WeekEnd +WorkDays, all these add up together to the EndDay. sample data: Start date Work days 05.21.2008 9 11.05.2008 124 12.28.2008 35 02.07.2009 114 and the hollydays are in the form DD - MM (year - independant) DD MM 12 01 11 02 3 03 25 04 22 07 1 09 2 11 21 11 31 12 How should i approach such a problem? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get "EndDate" of a project?
I finally came up with this solution:
={WORKDAY(A2,B2,(DATE(YEAR(A2),$K$20:$K$28,$J$20:$ J$28)))} whe A2 = Starting Date B2 = Number of working days $K$20:$K$28 = Array of Months $J$20:$J$28 = Array od Days so i tried to make a virtual array generating the date from the year of the cell, days and months(of holidays) provided by the user and copied it down through the starting days of the projects. I am not sure if this is OK, so I need your thougths please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display "macro-free workbooks - VB project message" on closing Exc | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"Can't find project or library" after emailing files. | Excel Discussion (Misc queries) | |||
Excell error "Can't find Project or Library" Project VBAProject | Excel Worksheet Functions | |||
remove name of addin in Tools menu: "Document Project" | Excel Discussion (Misc queries) |