Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculation of end date
Hi all,
I am wondering how can I get the end date of project if i have the starting date, the workdays that it will need, the fact that a week have 5 working day and a list of holidays? 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. sample data: Start date Work days 05.21.2008 9 11.05.2008 124 12.28.2008 35 02.07.2009 114 and the hollyday are the form(year - independant) DD MM 12 1 11 2 3 3 25 4 22 7 1 9 2 11 21 11 31 12 How should i approach such a problem? Thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculation of end date
Look in Help for the WORKDAY() function. Post again in this same thread if
you have problems. -- Kind regards, Niek Otten Microsoft MVP - Excel "Totti" wrote in message ... Hi all, I am wondering how can I get the end date of project if i have the starting date, the workdays that it will need, the fact that a week have 5 working day and a list of holidays? 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. sample data: Start date Work days 05.21.2008 9 11.05.2008 124 12.28.2008 35 02.07.2009 114 and the hollyday are the form(year - independant) DD MM 12 1 11 2 3 3 25 4 22 7 1 9 2 11 21 11 31 12 How should i approach such a problem? Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculation of end date
Workday() must do the work but I
Just can not do it, it went so bad that at some point it ended up giving me dates in 1900 :-) could anyone please provide more help? like how to use workday() in this settings? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculation of end date
All dates should be "real" Excel dates. If you format 05.21.2008 as general,
you should get 39589. If it remains as it was, then it is text, not a date. You can change the text to dates with =DATEVALUE(SUBSTITUTE(A1,".","/")) Format as date. Your workdays should be real numbers too, not text. Multiply by 1 to make them numbers, if necessary. Your holiday dates should be real Excel dates too, including the correct year. -- Kind regards, Niek Otten Microsoft MVP - Excel "Totti" wrote in message ... Hi all, I am wondering how can I get the end date of project if i have the starting date, the workdays that it will need, the fact that a week have 5 working day and a list of holidays? 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. sample data: Start date Work days 05.21.2008 9 11.05.2008 124 12.28.2008 35 02.07.2009 114 and the hollyday are the form(year - independant) DD MM 12 1 11 2 3 3 25 4 22 7 1 9 2 11 21 11 31 12 How should i approach such a problem? Thanks |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculation of end date
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 | |||
Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
date calculation | Excel Worksheet Functions | |||
Date Calculation | Excel Worksheet Functions | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) |