Dates and IFs Query

[jaredf]

Hi,

I'm having a bit of trouble with the IF formula in my spreadsheet, see attached.

I'm trying to populate the green colored cells with the return value in column A. Only if the start and end dates in column B and C fall within a particular week. I have highlighted the cells in red where I'm experiencing the problems.

For example; row 2 has a start and end date from 20-May to 30-Jun. I would like this to be shown in the spreadsheet across the corresponding weeks, however, cell Q2 does not return the value in column A, even though this week falls within the start and end dates in columns B and C.

Not sure if the formula I have done is wrong for this type of update but will appreciate your help, my IF formula knowledge is limited.

The attached is example data and may not seem significant but in practice this spreadsheet is much larger and more importantly the start and end dates are likely to change throughout the course of the year, so having the green colored cells update automatically will save me a huge amount of time.

Appreciate your Excel help! Please let me know if you need anything else. I hope I have explained my problem well enough.

Thanks,
J
dates example.xlsx

[mcmahobt]

Would this work? Paste into F2 and drag across/down:

Please Login or Register  to view this content.

[jaredf]

Hi,

Thanks for the speedy response. This has worked for updating one of the red cells I had a problem with but not the rest. It worked for cell L3, but not for Q2..

Its when the date starts during the middle of any particular week is when the problem occurs.

[mcmahobt]

It doesn't work for Q2 because Q2 doesn't fall within the range 5/20/2015 to 6/30/2015, and therefore returns as "". Also, your B4 and C4 values look incorrect. 4/13/2015 is after 4/5/2015.

[jaredf]

Yes you are right, I have populated the example data incorrectly there for B4 and C4, sorry for this. However, for cell Q2, this week starts on 18-May and finishes on 24-May. The start date for this row is 20-May so it starts mid week which I think is causing the issue? Appreciate your help with this

[mcmahobt]

Okay then will something like this work? In F2 paste this and drag through U2:

Please Login or Register  to view this content.

Then in F3 paste this and drag down/across to fill:

Please Login or Register  to view this content.

[jaredf]

Thanks. Definitely getting closer, some of the return values are still missing across a few cells, see attached. Should I be displaying the dates differently to make it easier? I'm not sure to be honest, I appreciate you helping
dates example (2).xlsx

[mcmahobt]

It seems like the problem stems from the exception of Q2. Maybe if you explain what your spreadsheet is being used for, I can assist better.

[jaredf]

It's like a project road map. Each row we'll have the different sub-projects listed in column A, with their start and end dates in columns B and C. During the course of the year these projects start and end dates will change because of external factors. From this document it's very easy for us to plan each project if one is delayed or vice versa (completed ahead of time). It's visual purpose plays a big role in planning. For example; if project c is delayed a few weeks how does this look if we are to start at this date or another date. It becomes easier for us to visualize how we're progressing against the road map; where are we behind or ahead because some projects cannot start until some have finished etc...

Hope this adds some more detail. Let me know if you need anything else. Thanks again

[mcmahobt]

This should do it then. It was helpful knowing the background of the project because I was able to roll up the two previous formulas into one succinct one that should do the trick. Paste into F2 and drag across/down:

Please Login or Register  to view this content.

[daddylonglegs]

I would probably try to avoid using WEEKNUM function in these type of calculations because it won't work as required when comparing dates at the start end of the year, e.g. you might expect Wed 31/12/2014 to be in the same week as the following day, Thu 1/1/2015.....but WEEKNUM says they are in different weeks.

This formula in F2 copied across and down gives the required results

=IF(AND($B2-WEEKDAY($B2,3)<=F$1,F$1<=$C2),$A2,"")

[mcmahobt]

I would probably try to avoid using WEEKNUM function in these type of calculations because it won't work as required when comparing dates at the start end of the year, e.g. you might expect Wed 31/12/2014 to be in the same week as the following day, Thu 1/1/2015.....but WEEKNUM says they are in different weeks.

This formula in F2 copied across and down gives the required results

=IF(AND($B2-WEEKDAY($B2,3)<=F$1,F$1<=$C2),$A2,"")

Didn't think of that scenario, good catch. Excel is good for a lot of things, but I try and stick to MS Project when doing any IMS scheduling.

[jaredf]

Thank you both for your help, this has worked! This has and will help me a lot, thanks again!