Search in a range of cells to see if date is in the same week number

[iankerry]

Hi there,

I have been trying to work on this but can't quite get there.

I have a spreadsheet with a tab on for every employee. When they request a days leave the spreadsheet gets updated.

There is an OVERVIEW sheet, and I want it to anyone who is on holiday THIS WEEK and who is on holiday next week.

So the name of someone in one cell, then in the next some text saying they are on holiday or blank.

The formula i have tried is this:

=IF(WEEKNUM(employee1!A5)=WEEKNUM(TODAY()),"ON HOLS","")


which works if the one cell on the employee tab has a holiday in this numweek.

But if i want the formula to check all the possible cells on each employees tab, from A1:A33, what formula would i use to check if they are holiday this week, or next week?

Thanks for any ideas.

Ian

[Glenn Kennedy]

1. Why not have 1 sheet for ALL employees, with an additional column showing Employee name? One BIG sheet is much easier to work with than lots of little ones.


2. If you're stuck with this bad design, can the employee name be recorded IN the sheet, rather than ONLY in the Tab name?

3. How many employees?

[iankerry]

Hi Glenn, thanks for your reply.

The attachment i sent was only meant to show people of the forum what i am trying to do with this formula. It wasnt supposed to be the finished spreadsheet.

So yes i could have it all on one big sheet, and i could add peoples names - we only have 7 employees.

But did you have any thoughts on the formula i might need to achieve the result i need?

best wishes
ian

[Glenn Kennedy]

Yes, of course. But before doing anything YOU needed to define what was OK with you.

[Glenn Kennedy]

Since you have O365, you should take advantage of its functionality.

I put all leave into one sheet (Leave) and included a lookup Table (in case some - part timers) have a different leave allowance. I sorted them into date order, as that's the logical way they'd be kept.

Column D keeps the individual's running total.
=VLOOKUP(A5,$J$2:$K$4,2,FALSE)-SUMIF(C$5:C5,C5)

copied down.



On the summary, a list of unique employees is returned by:

=UNIQUE(FILTER(Leave!A5:A100,Leave!A5:A100<>""))

Their hours left, by:

=BYROW(B8:B10,LAMBDA(x,TAKE(FILTER(Leave!D5:D100,Leave!A5:A100=x),-1)))

Those absent this week, by:

=UNIQUE(FILTER(Leave!A5:A100,(Leave!B5:B100>=B1)*(Leave!B5:B100<B1+7)))

These last 3 do not need to be dragged. Just put them where you want the results to be.

If you need more than 95 leave events, increase the 100 s to something sensible that works.

[iankerry]

Hey Glenn, many thanks for taking the time and trouble to reply so fully, with the attachment.

I will take a good look at the formulas you have used - i haven't used vlookup before for example.

I am sure this will sort out my problem,

thanks again

Ian

[Glenn Kennedy]

Reprt back if there are problems

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.