Weekend rota Planning (generate values based on criteria/date/shift pattern)

Please anyone can help ? Can some help with formula to be able to generate time and green color in certain cells automatically ones the date is changed based on entered values or based on 4on 4of shift pattern, more explanation included on attached workbook.

YOu should explain what you want help with in the opening post, please.

Remove ALL expected results from the table, then this in E7:

Please Login or Register  to view this content.

CF rule for E7:

=E7<>""

Applies to: =$E$7:$L$11

The results on the right-hand side are entered manually.

Change E5 to this to pull dates from the table on the right:

=O5

The formula I gave you will reflect whatever is in that table (O4:T8).

Try this:

Please Login or Register  to view this content.

Also, in E6:

=UPPER(TEXT(E5,"dddd"))

Copy to the other day cells.

If you decided to extend your grid to a full week (7 days), then you'd need this:

Please Login or Register  to view this content.

and you'd need to extent the Applies to range in the CF rule as well.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Seeen, gone, no reply ...

Please see the extra help in post #9. It also tells you how to sign off the thread.

there will be more employees added to the list/table

You'd just change the range accordingly.

Is it possible that this values can be altered manually without messing whole formula ??

No.

I found another issue that sometime certain employees will finish shift different time than 18:00

You must have known this at the start - why didn't you mention it and how on Earth would Excel even know from the data as you have presented it? The times are not associated with the employees anywhere in the workbook.

I have a horrible feeling that I've wasted quite a bit of time on a set of data that is a long way from complete.

Also I was wondering if you will be able to give brief explanation to your formula ?

I don't think there's any point given what you have just reveealed.

You need to provide a much more realistic set of sample data - it's going to be a case of starting from scratch.

I shall probably leave this for someone else to look at - I feel I've spent enough time on it already (just so that you know).

This is no different to the original workbook you provided. Once again, shift times are not associated anywhere in that workbook with individual employees. I cannot make the formula work any differently witbout that information.

A cell can contain a formula or hard-coded text - not both. The minute you override a formula, the formula is gone forever. You cannot override the formula I provided, anyway, as it's a dynamic array.

Since you are unable to provide a dataset that shows clearly the times that each individual works on their shifts, I cannot help you further. I am sorry, but that's the way it is.

There isn't any doubt about what you want. My formula does exactly whan you have shown. What it doesn't (and cannot) do is put in different shift times for different people.

I notice that your latest worksheet doesn't account for this, either. I am going to step aside now as it's realy evening and I'm going offline. Maybe someone else will be able to explain the issue to you.

That wasn’t what I meant, but if you are insistent on overwriting a formula, then it’s going to need a different approach. If you’ve had no joy by tomorrow, I’ll have another think.

A possible option is to have a different number for a different shift time so 1 = 06:00 - 18:00 , 2 = 07:00 - 16:00 etc.

Given this, I don't know if this can be incorporated into a formula that was provided by Ali which is way beyond my capabilities.

Failing this, is a VBA solution an option ?

I could do that, yes, I think, but the OP wants to be able to override entries, so a spill formula won’t work - it would need to be a copy across and down one.

So John's suggestion is a non-starter - is this correct?

Will the employees always be in the same order in the lookup table as they are on the summary table?

In E7 copied across and down:

=LET(x,IFNA(INDEX($P$5:$T$53,MATCH(E$5,$O$5:$O$53,0),MATCH($D7,$P$4:$T$4,0)),""),IF(ISODD(COLUMN()),IF(x=0,"","06:00"),IF(ISEVEN(COLUMN()),IF(D7<>"","18:00",""))))

Yes, I did, didn't I?

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.