Date calculation for next week

LisaB:-)

Hi, unfortunately it doesn't though.

'That would be because 9/9 does not fall inside of the range. Today plus 7 is 10/9. The formula is looking for things that fall on or between 7 days from now and 14 days from now. Not necessarily next week, but between one and two weeks from today.

If you are looking for specific weeks, you will need to take into account week numbers instead of days.

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

 

I'd like to explore the solution that he mentioned above, regarding specific weeks.  Any assistance would be very much appreciated!

 

Thank you, Lisa

Paul Newcome

Andree is correct in that the formulas will automatically update the week numbers on the back end every time the sheet is opened. However...

 

Based on your latest response, it looks like your are pulling Tuesday through Monday. We may need a different workaround to account for this. Can you confirm?

Paul Newcome

The above formula would be the solution to look at everything that is happening "next week". The WEEKNUMBER function is what allows for that.

LisaB:-)

HI, sorry this has got a little confused.  I need it to report for Monday to Friday next week, providing that it will report for the whole of Friday, that is, up until midnight.  (If there's any possibility that it will miss things due to, perhaps, time zone differences, then it might be safer to have it report from Monday to Sunday.)

So on Friday this week, I'd like to run a report which captures everything that's happening next week.  Then, next Friday, I'd like to do the same, except of course, that would be the following week (w/c 16 - 20 September).  And so on.

Thanks for your help.

Paul Newcome

Ok. Then my WEEKNUMBER formula should work for you.

 

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

.

The WEEKNUMBER function pulls Monday through Sunday, so there shouldn't be anything missed.

LisaB:-)

Hi, it's still picking up Start 19/09/2019 to Finish 02/10/2019, when I want the date range to be Start 09/09 to Finish 15/09/.  But, I've just had a lightbulb moment - are you telling me that when I run it on Friday, it will THEN pick up the date range 09/09 to 15/09?  So it's counting a week from each new day?

If that's the case though, then it's currently returning something like 2 weeks, rather than 1 week into the future.

Many thanks, Lisa

Paul Newcome

My apologies. I thought I had read somewhere that you were wanting the next two weeks. I must have gotten my posts mixed up. Try this...

 

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

LisaB:-)

Hi again Paul, thanks for your continued support.  The latest formula 

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

is returning a row with the start of 26/08 and a finish of 09/09; but also a row with the start date of 19/9 and a finish of 20/9, which falls outside next week, as today is 9/9.  That's almost a four week period.  Could you look at it again?

LisaB:-)

Hi, I've just stumbled upon this old thread.  I've changed the '5' to '7' and it seems to be working perfectly on my sheet.  

Thanks for all your help - I think we're done! 

Paul Newcome

I was under the impression that you wanted to see the projects that have a start date of next week, an end date of next week, or are in progress during next week even if the start and end dates didn't fall during next week. 19/9 is next Thursday, therefore it would be included in the formula.

 

The first set of dates should not be included based on the above logic. It is being included because of an error on my part consisting of a misplaced parenthesis.

 

The corrected parenthesis placement would be as follows:

 

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

Andrée Starå

Excellent!

Glad you got it working!

Paul Newcome

My apologies. I must have gotten things mixed up. I thought you had said that you wanted Monday - Friday of next week and NOT just looking at 7 days from today's date. Otherwise we would have gone back to the original formula and worked from there instead of going down the WEEKNUMBER rabbit hole.

 

Glad you got it working though. 

LisaB:-)

Hi Paul

I did want Monday - Friday next week, and not just 7 days from today's date, but I figured that as I would be running the report every Friday, 7 days from 'today' would work just as well.  Having said that, I revisited the post where you moved the parenthesis in the formula, and that works really well, so I'm going to use that.  The only thing is it doesn't seem to return results including parent rows, but unless that's an easy fix, I can live without it.

Many thanks, Lisa

Paul Newcome

Are the dates in the parent rows a summary of the children rows? If so, it SHOULD meet the criteria of the overlapping dates. Can you provide a screenshot of exactly what you mean with sensitive/confidential data replaced with "dummy data" or removed/hidden?

LisaB:-)

Hi, I think the formula should have captured the parent here, because it should be searching between 16 - 22 September 2019.

Many thanks, Lisa.