Calendar Populate between dates

[Martinch]

Can anyone help please. I've tried IFAND, IFOR over and over again and I've not been successful.

I'm looking for a formula that will self populate the calendar as per the attached.

Any help much appreciated please.

Kind Regards


Martin

[DystopiaRelic]

Hi Martin,

Your request is a little unclear, so I've made a few assumptions...

Assuming that each row relates to a specific task (i.e. row 5 pertains to one task, (say, task A) and row 6 pertains to another (say, task B). If that is the case, I've moved the date summaries up a row so they match for ease, but in practice it doesn't matter where they sit).

Also assuming that you are looking to return the first and last instances of a task.

If that is the case, I've attached an example using XLOOKUPS, but amended amended between returning the first and last value (1, -1). You will also need to use a method to distinguish whether the activity is the Check 1 or Final. To do so, I've changed the value to 2 for the final check.

Hope that helps.

[Martinch]

Many thanks. This would work if cells AO6, AP6 had a 1 entered.

I need all numbers to be a 1 preferably at end state as the digits are work day counts, but I can add a duplicate sheet and add an IF statement if necessary.

Have a look at Sheet1, utopia for this project would be to make the first digit a 0 also but again I can add an IF statement in a duplicate sheet if necessary.

I really appreciate the help and apologies if the ask was not clear. I look forwards to hearing back from you.

Kind Regards

Martin

[Pete_UK]

With the table data in columns A to E aligned with row 5, you can use this formula in G5:

=IF(WEEKDAY(G$3,2)>5,"",IF(AND(G$3>=$B5,G$3<=$C5),1,IF(AND(G$3>=$D5,G$3<=$E5),2,"")))

Copy across and down as required.

Hope this helps.

Pete

[Martinch]

Many thanks for this. The second set of date values comes out as a 2, is it possible to have this as a 1 also please.
All of the 1's indicate 1 days work.
I can duplicate the work sheet and use IF statements to set to 1, but would be better if the formula could handle this.
Kind Regards, much appreciated.

Martin

[Martinch]

Hello again, sorry I have just adjusted the formula. It works fine. Thank you so much for your help.

[Pete_UK]

Your file showed a 2 for the second set of dates, but hopefully you have changed the 2 to a 1 near the end of the formula if that's what you really wanted.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete