Excel check if dates are under a range of week

  • Thread starter Thread starter Totti
  • Start date Start date
T

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?
 
"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."
 
... 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 ...
Click to expand...

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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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.
 
Back
Top