Using array formula to help with a networkdays holidays issue

[Sam Longstaff]

Hi,

I have an absence monitor spreadsheet that calculates the number of days that a person has been off work taking in to consideration any days they do not work. I had been tackling this by having a table of all Mon-Fri dates for a number of years (off days). Then for each part time staff member I would make a table that had the same number of columns as days of the week they don't work. This would then be populated via a lookup with the dates for these off days. This would then form the list of holiday dates when using a networkdays formula to calculate the number of work days off during a period of absence.




The issue with this is it a bit labour intensive as I have to make bespoke tables for each part time member of staff and then if they change the days they work I have to revisit it an amend accordingly. What I am aiming to have is a set template for my workbook that uses the same section in each staff members own worksheet to populate with off days that act as the [Holidays] list.




I am struggling with the array formula that would look up the staff info table (N.B. off days are zero in this table) and find the off day(s) for each staff member. Then once I have this I can use the position(s) to pull in the dates from the off days table.




Can anyone help please?

 

[Peter Bartholomew]

The formula
= INDEX( workingDays, MATCH( Name, Staff, 0 ), {1,2,3,4,5} )
would show the staff member's work pattern for Mon-Fri.

If you had a specific array of dates in mind
= IFERROR( INDEX( workingDays, MATCH( Name, Staff, 0 ), WEEKDAY(dates,2) ), 0 )
would return the expected pattern of workdays. The IFERROR is needed because Saturdays and Sundays cause errors.

The new XLOOKUP function works well but is not generally available. VLOOKUP seems to fail completely when some of the data it tries to return is outside the bounds of the lookup range.

 

[Sam Longstaff]

Thanks for your reply. That doesn't seem to do what I'm looking for but perhaps I have not worded my post very well. I am looking for a formula to return the column positions that show where a staff member has a "0" in the table. This would mean someone who doesn't work mon/tue would have a return in two consecutive cells of "1", "2". Someone who doesn't work Tue/Wed would have a return in two consecutive cells of "2","3". Someone who doesn't work Mon-Wed would have a return in three consecutive cells of "1","2","3".

I feel that an array formula is needed to facilitate the lookup of occurrences of "0".
Any help gratefully received.

 

[NARAYANK991]

Hi ,

I am not clear on what is required.

First , what is the table on the tab named Off days ? Which employee does it refer to ?

Second , you have 31 staff members ; will you have 31 tabs for them , one for each staff member ?

Third , you have created a tab for employee Sam ; will what ever formulas are developed for Sam be reproduced for all other employees ?

Lastly , in which cells in which tab do you want the formula ?

Narayan

 

[Sam Longstaff]

Hi ,

I am not clear on what is required.

First , what is the table on the tab named Off days ? Which employee does it refer to ?
The off days table are dates for mon-Fri through the years so that if a staff member doesn't work on a Monday but is absent for over a week the networkdays formula can look up the off days table in the relevant column (in this case Monday) and use this as the range for the [Holidays] part of the networkdays formula.

Second , you have 31 staff members ; will you have 31 tabs for them , one for each staff member ?
Yes.

Third , you have created a tab for employee Sam ; will what ever formulas are developed for Sam be reproduced for all other employees ?
The tabs will all have the same template with formulas in but will look up the staff info table based on the A1 cell contents.

Lastly , in which cells in which tab do you want the formula ?
They can go anywhere that is not in a table at the moment, I will move them later.

Narayan
[/QUOTE]

Thank you for your reply.

 

[NARAYANK991]

Hi ,

Are you looking for something like this ?

What happens in cases where the employee has taken half the day off ?

Narayan

 

[Sam Longstaff]

Hi ,

Are you looking for something like this ?
Yes sort of. Is it possible to get the returns to appear in the first columns? see attached (also excuse the Mon-Fri bit at the top I forgot to remove that)

What happens in cases where the employee has taken half the day off ?
The 0.5 indicates where someone works part of the day, these are considered whole days in absences. It is only the "0" that need to be considered

Narayan

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan