Excel check if dates are under a range of week

[Totti]

Hi everyone, i have the following problem:
i have raw data where there tasks to be accomplished with a starting
and ending date and man hours i want to check if the data falls in a
range of weeks thats a sample of my data
WP ACT MH SD ED 01-Jan-10 08-Jan-10 15-Jan-10 22-Jan-10 29-Jan-10 05-
Feb-10 12-Feb-10
Civil Excavation 75.53 01-Jan-10 12-Jan-10
Civil Blinding 5.00 11-Jan-10 29-Jan-10
Elec Cable Tray 25.00 20-Jan-10 14-Feb-10
Elec Wiring Device 4.00 31-Jan-10 04-Mar-10
Plum Sanitary Fixture 6.00 23-Feb-10 03-Apr-10
HVAC Ducting 80.00 29-Mar-10 14-May-10
Civil Reinforcment 120.00 23-May-10 15-Jul-10
Civil Formwork 18.00 24-Jun-10 23-Aug-10
Elec Cabling and wiring 120.00 07-Aug-10 13-Oct-10
Elec Pannel Board 3.00 12-Oct-10 25-Dec-10
Plum fire piping 120.00 24-Oct-10 13-Jan-11
HVAC chiller piping 35.00 10-Nov-10 06-Feb-11
you can copy and paste into excel and see how it is,
where i am stuck is that i want a 1 where the starting and ending date
fall under a week or a fraction of a week, like if the days are more
than 7 they ll take 2 weeks so 1 and 1 under 2 consecutive dates,
while if the days are less than 7 they will fall under 1 week and have
a value of one under that wile everything else is a 0 value, could
anyone help me do that please?

 

[Don Guillett Excel MVP]

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[zvkmpw]

... if the days are more

than 7 they ll take 2 weeks so 1 and 1 under 2 consecutive dates,
while if the days are less than 7 they will fall under 1 week and have
a value of one under that wile everything else is a 0 value ...

This is the part I don't understand. Suppose the start date is Jan 6
and the end date is Jan 9. Then the start and end dates are in two
different weeks, according to the week boundaries in the header row.
But it's a four-day task. What's the desired outcome in a case like
this?

P.S.: It seems that the first three columns don't enter into
calculating the ones and zeros.

 

[Totti]

This is the part I don't understand. Suppose the start date is Jan 6
and the end date is Jan 9. Then the start and end dates are in two
different weeks, according to the week boundaries in the header row.
But it's a four-day task. What's the desired outcome in a case like
this?

P.S.: It seems that the first three columns don't enter into
calculating the ones and zeros.

You are right, i should have made that clear, if its a less than 7
days task than its gonna put a 1 under the starting date as well so
its like (i started somewhere this week and ended as well)
Actually i dont know if at all i am approaching the problem correctly
because i m consuming much time and nowhere to get, The thing is that
i need to check under which weeks this task falls like i have week 1,
2, 3, and so on, so:
in case where duration is between start and end of week, put 1
in case where duration is under this week and end date is greater than
the end of the week(or start of next week) keep putting ones till the
week that has a date greater than end date.
I m a schedueling and cost control engineer we used to do these things
on primavera but i m asked to do it in Excel now and i m stuck thanks
for your help

 

[zvkmpw]

in case where duration is under this week and end date is greater than

the end of the week(or start of next week) keep putting ones till the
week that has a date greater than end date.

Presumably, the same is true for longer durations too. For example, a
duration of 9 days can touch three weeks.

With this additional information, I’ve outlined an approach below, if
I understand the requirements. Should the requirements change, it can
hopefully modified readily.

First the setup.

Column D has the starting dates, beginning in row 2.

Column E has the ending dates, beginning in row 2.

Row 1 has the weekly dates, beginning in column F.
The dates here must go at least one week past the latest ending
date.

Next, the cell F2 is the upper-left corner of the zero/one array.
Enter this there:
=N(AND($D2<G$1,$E2>=F$1))

Extend F2 to the entire array.

Hope this helps.