Date calculation for next week

LisaB:-)

Hi, I want to run a report that will return all rows for tasks that need to be worked on next week.  So that will include tasks where the start date is next week, or the end date is next week, or simply tasks that are ongoing through next week, with neither a start or end date occurring during the week.

I have a hunch this might include a conditionally formatted check-box, but not sure where to start with the calculation.  Any help will be very much appreciated.

Tks Lisa

Paul Newcome

You would use something like this...

 

=IF(AND([End Date]@row >= low end of date range, [Start Date]@row <= high end of date range), 1)

.

Obviously you will want to swap out the underlined text for however you want to establish your date range low and high ends, but basically you want to set the formula to check the box if the end date is greater than or equal to the start of the week and the start date is less than or equal to the end of the week.

LisaB:-)

Thanks!  Unfortunately, that's not working for me - I might have the date syntax wrong.  I'm using:

 

=IF(AND(Finish@row >= 9 / 9 / 2019, Start@row <= 13 / 9 / 2019), 1)

 

Would be very grateful if you could advise.  

Paul Newcome

You are correct in that your date syntax is incorrect.

 

=IF(AND(Finish@row >= DATE(2019, 9, 9), Start@row <= DATE(2019, 9, 13)), 1)

LisaB:-)

This. Is. AMAZING!  Thank you so much.  Before asking on here, I did a lot of searching for a solution, and couldn't find anything which matched exactly what I wanted.  I was surprised about that, because surely everyone wants a report which tells them what's coming next week, and for the next two weeks, etc?

Thanks again :-)

LisaB:-)

...Thinking about it though, do you know of a way that the calculation would work without entering the dates?  Because I would have to do that every week to run these reports.  Maybe some kind of COUNT?

Andrée Starå

Hi Lisa,

You could add a helper column that for example, could check a box if it should be included in the report. The formula in the cell would look at which week it is today and look in the past and the future as needed.

Would that work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

LisaB:-)

Hi, thanks for replying.  Yes, I'm using a helper column, but I don't know what the formula in the cell should be.  It needs to look at the next 7 days - any ideas?

Paul Newcome

Ok. To use a checkbox column to compare to today's date, we would use a formula similar to this...

 

=IF(AND(Finish@row >= TODAY(7), Start@row <= TODAY(14)), 1)

Andrée Starå

Happy to help!

I saw that Paul answered already! That should do it!

Let me know if I can help with anything else!

Best,

Andrée

LisaB:-)

Hmm, that's not working.  My Start is 26/08/19 and my Finish is 09/09/2019.  Would that be because it's calculating on the dates, and not the time between the two dates?

Really appreciate your help, thank you.

Paul Newcome

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)

LisaB:-)

Hi, thanks for this, it's definitely a move in the right direction.  I do need it to return everything that happens between 9 and 15 September, and then next week I'll need it to return everything that happens between 16 - 22 September, etc etc. How would the formula work using week numbers?  Would I have to enter week numbers manually?

I really appreciate your continued support, thank you.

Andrée Starå

Pauls formula will update every time the sheet is opened and look at the current week. No need to change anything.

Make sense?

LisaB:-)

Hi, the problem is that I need it to report on Monday - Friday next week, every week, not today + 7 days (which is Wednesday - Wednesday).

Many thanks, Lisa

Andrée Starå

Paul's latest formula should do that.