Date and Repeating Date in a plan - IF OR Formula?

Hi folks.

I am struggling a little with a plan I am trying to produce. It is a 52 week planner, ultimately for a period of up 10 years... for now, I will keep it simple and look at providing the example for just 52 weeks.

In the attached example I have managed to successfully put the number of minutes in the correct Week based on the 'Next Due Date' indicated in column J.

I believe I need to use an OR statement, as well as the IF now as what I would also like to do is show the same number of minutes based on the repetition of the item, based on the Pitch Days shown in column K.

For example, Row 7 shows a repeat of every 30 days, so ideally I need '5' to appear on the next due date (03/09/2018) which it is , PLUS I need to show it every 30 days after that.

I really am lost and wondering if the 'IF OR' approach is the right one or not? I am trying to avoid VBA at this point if at all possible!

Note, blank columns are left in as this is dummy data which I can then plug the formula back in to my correct worksheet!

All guidance gratefully received. Please let me know if I need to provide any further information.

Enter this formula in cell L7 and copy it right to BK7 and down to L10:BK10.

Formula:

Please Login or Register  to view this content.

Thank you Tsjallie for your very prompt answer and your wonderful solution. I hadnt considered week number

I noticed one issue... when the year changes (2018 to 2019) the week number reverts back to Week 1 (of course) however the plan week continues to increment. Is there a way around this?

Kind regards

Indeed at year change there's an issue because weeknumbers start again at 1. Clean forgot about that.
So need to consider the year too. Working on that.

BTW, are these pitch days calendar days or working days?

Thank you, I do appreciate the help.
They are calendar days, the system the data comes from is not sophisticated enough to ignore weekends!
Thank you for asking

Give it another try with this formula. Enter it in L7 and copy it right and down.

Formula:

Please Login or Register  to view this content.

FANTASTIC! Thank you so much. Great response and perfect solution. Thank you for giving me your time.

Morning
Sorry about this. I am getting a circular reference error when I expand the formula for the rest of the sheet.
In the attached example the error reporting is showing $L8 as the circular reference.
Any idea?
Thank you again

No attachment ...

I was just editing the post... didnt realise the file was over 1 MB so trimmed the data. Thank you AliGW

There was a bad reference in the countif. Corrected that and also introduced a dummy column.
This to avoid the situation where the value in column K happens to be the same as in column H.
That would give eroneous results.

Should be solved in the attached wb.

Thank you for that and for the guidance note also.
Sorry to be a pain but I have also just noticed another item...
When the due date occurs before the plan start date, it omits placing the hours in the right cell. Only appears ot happen in some cases though!
In Date Test 3, Row 23 has a 30 day pitch, starting 09/08/2018... the monthly time is not pulled through, yet,
On Row 24, the due date is once again prior to the start date, yet the annual value is showing in the next due date cell?!!!
Its a little baffling!
Any idea?
Thank you for your continued support

That indeed is a brain crusher. Not a clue what's causing this yet
Wish me a brainwave

OK, so my thought is that because we are using the week number, which obviously resets after 52. I think this is why I was going down the straight date route rather than a week number. Before is obvious, between is also

That help?

Good Morning.
So, going back to the idea of using dates... The following two statements work individually:

This line checks to see if the due date falls between the week commencing and the next week commencing

Please Login or Register  to view this content.

This line checks to see if the next due date PLUS the pitch date falls between the week commencing and the next week commencing

Please Login or Register  to view this content.

So, the question now is, how do I combine them with an OR formula?

Does that provide the necessary inspiration?

I have attached Date Test 4 as the example file

Further note to the above. I have managed to combine the formula above, my result is

Please Login or Register  to view this content.

My issue now is that this only picks up the first visit, not the repeat based on the pitch indication!
Still scratching my head!

I think I figured it out.
Actualy the formula comes down to finding out if the StartDate (column J) + x times the frequency (column K) fits in the week of the date in a calendar column.
If true it puts the time (column H) in the cell else leave the cell empty.
The x in the equation is in fact the multiplier of the difference in days between the startdate and the calendar date.
That gives:
If [StartDate] + (round(([CalendarDate]-[StartDate]) / [Frequency],0) * [Frequency]) >= [CalendarDate] AND
[StartDate] + (round(([CalendarDate]-[StartDate]) / [Frequency],0) * [Frequency]) Then
[Time]
Else
""
This gives the following formula for cell M7:

Formula:

Please Login or Register  to view this content.

Full marks to you! That appears to have worked, so once again... thank you so very much!

REALLY appreciate your time and effort

Your welcome. It's been much more fun than Sudoku (or Dutch television)
Thx for the rep.

Ha Ha and you're welcome. Well deserved and credit where credit is due