IF/INDEX/COLLECT

amber.lange

Hi, I've been struggling with a formula which I believe needs IF/INDEX/COLLECT as well, but maybe I'm wrong.

We are going to collect daily time entries for all employees into a sheet and provide this data on a weekly bases to Payroll through a report. Our weeks run Sunday through Saturday with payroll processing occurring on Monday.

Employees get paid double time if the day worked is a Sunday and they worked the day prior, Saturday. At this time, I'm trying to check a box automatically that indicates to payroll that the employee gets DT. I created some helper columns thinking that would make it easier, but I'm still stumped.

In the image below, I would be looking to see if Employee@row's [Employee Daily Status]=Worked for 11/4/23 and if [Supervisor approval]=Approved, if so, check box for DT on row for time card entry of 11/5/23. The formula shown is for the [Worked Saturday] column.

HHansford

Hi @amber.lange,

I think you just need an IF/AND statement for this one. The COLLECT function pulls in the data from another column/sheet and I think you are looking to put a checkmark in the DT column.

Paul Newcome

@amber.lange Try something like this in the DT column (update column names as needed):

=IF(AND(DAY([Date Column]@row) = 1, [Employee Status]@row = "Worked", COUNTIFS([Date Column]:[Date Column], @cell = [Date Column]@row - 1, Employee:Employee, @cell = Employee@row, [Employee Status]:[Employee Status], @cell = "Worked", [Supervisor approval]:[Supervisor approval], @cell = "Approved")> 0), 1)

amber.lange

Hi @Paul Newcome , I'm finally getting back into working on this sheet. I don't think I can use the DAY function since I'm looking at the day of the week, not the day of the month. So, this is the formula I tried with WEEKDAY but it returns no results, even though I have manipulated the data that it should show DT for 11/5/23.

=IF(AND(WEEKDAY([Date for Time Card Entry]@row) = 1, [Employee Status]@row = "Worked", COUNTIFS([Date for Time Card Entry]:[Date for Time Card Entry], @cell = [Date for Time Card Entry]@row - 1, Employee:Employee, @cell = Employee@row, [Employee Status]:[Employee Status], @cell = "Worked", [Supervisor approval]:[Supervisor approval], @cell = "Approved") > 0), 1, 0)

amber.lange

@Paul Newcome I worked on this a little more and it looks like it's correct now. Thank you for your help!!

=IF(AND(WEEKDAY([Date for Time Card Entry]@row) = 1, [Employee Daily Status]@row = "Worked", COUNTIFS([Date for Time Card Entry]:[Date for Time Card Entry], @cell = [Date for Time Card Entry]@row - 1, Employee:Employee, @cell = Employee@row, [Employee Daily Status]:[Employee Daily Status], @cell = "Worked", [Supervisor approval]:[Supervisor approval], @cell = "Approved") > 0), 1, 0)