Related Posts
Rising Star
Does Oracle have holiday shutdown?
Experience in shareholder services for GS?
Sundial Growers 🏄🏻♂️🏄🏻♂️🏄🏻♂️
Ok last one for real
Additional Posts in Excel Genius
Visual Storyteller
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Subject Expert
Hum, so your “shift” is like 10 days long - what would you want the formula to do if the employee “603” have two rows, both during the shift “a” timeframe?
Assuming the obvious, you can just use =FILTER() and control the criteria. Note that if your database have multiple entries, you may have a bunch of same shift rows - you may add a =UNIQUE() there to fix this. Please note that I’m assuming that there is no shift overlap (aka shift “a” and “b” don’t overlap for the same employee)
So, in excel terms:
=UNIQUE(FILTER(shift_range,(employee_nr_range=employee_nr)*
(start_date_range<=date)*
(end_date_range>=date),”not found”))
Where
Shift_range: the column where you have the shift in sheet 2
Employee_nr_range: employee_nr column in sheet 2
Employee_nr: the employee number in sheet 1
(Start date and end date ranges are the same)
Date: the date in sheet 1
And I’ve added “not found” to indicate when there is no shift that matches the date and employee number
Subject Expert
What I mean above is - id you add the parenthesis the formula will work as intended, however if your date is messed up, excel will not properly recognize if a date is more than another one properly
Try =MONTH(date) and see if the output is correct (if you use =MONTH(01-10-2022), excel will return 01 as the month if the date is formated as mm-dd-yy, the common for americans)
I have quite literally never heard of a Payroll and Data officer in my life. What does your role involve
I work for a company with 1700 employees staffed. We are responsible to make sure that all payroll related payments are made correctly. Employees are not over or under paid and building payment files for certain payments.
Use vlookup on empl nr
Vlookup is not helping cause 1 employee might have 2 or more entries on sheet 2 and I need to have the shift for a specific date. Except of there is a way to die a Vlookup on twee values. The employee nr and the date?
So employees have a permanent shift assigned to them when they are appointed.
Sometimes business requires the employee to work on a different shift and then the employee gets substituted to a different shift.
. Employee 603 can for instance work on shift a from 1 to 8 Oct and then work shift C from 13 Oct to 31 Oct.
I need to know that of employee 603 has clocked in on the 7th of October. Which shift did he work.
Subject Expert
I’ve edited the answer above - sorry, clicked “post” before I’ve finished, mb