Date calculation for next week

Paul Newcome

What is the exact formula you are using and exactly which row(s) is the formula not capturing?

 

If you are using the WEEKNUMBER solution, you will not capture that top red parent row because we are looking at strictly week numbers. We will need to build in something that also takes into account the years.

 

The formula will not pick up that top row because the week number for the finish date is actually 26. 

 

Let me know. I have another idea that will take into account specific dates and only requires a couple of additional helper columns.

LisaB:-)

This the formula I'm using: =IF(AND(WEEKNUMBER(Finish@row) >= WEEKNUMBER(TODAY()) + 1, WEEKNUMBER(Start@row) <= WEEKNUMBER(TODAY()) + 1), 1)

I could live without the parent rows being included, because the important detail would be contained in the child rows.  

Thank you, Lisa

Paul Newcome

No worries. We just need to include a YEAR function with each set of dates within the AND function.

 

=IF(AND(WEEKNUMBER(Finish@row) >= WEEKNUMBER(TODAY()) + 1, YEAR(Finish@row) >= YEAR(TODAY()), WEEKNUMBER(Start@row) <= WEEKNUMBER(TODAY()) + 1, YEAR(Start@row) <= YEAR(TODAY())), 1)

LisaB:-)

Hi, that's still not picking up the red row, but I think that's fine because the important detail is in the grey and white one underneath. I'm going to revert to the previous formula (with the parenthesis corrected) in the interests of a simpler, faster formula.

I think we can leave it there!  Thanks so much for all your help on this.

Lisa

Paul Newcome

Duh. I know why it isn't picking it up, but if you are fine with how it's working, that's what matters.

LisaB:-)

Hi Paul

Would you be so kind as to help me with a very similar formula?  I now need a formula which returns (into a checkbox) all rows where the date span includes yesterday, today and tomorrow.  It's to generate a report which will show everything happening 'right now' across several sheets. 

So it can't refer to Start Date and Finish Date, as if it did, it wouldn't pick up actions that are between those two dates - very similar as before.

Hoping you can help, Lisa

Paul Newcome

Try something like this...

 

=IF(AND([Start Date]@row <= TODAY(1), [Finish Date]@row >= TODAY(-1)), 1)

LisaB:-)

Worked like a charm 

Thank you!  Lisa

Paul Newcome

Excellent! Happy to help!