Hi, I am trying to edit an existing formula to enable it to add up "hours" (Column I) for a specific "job #" (Column D) for specific "payroll categories" (Column C) between a specified date parameter.
The existing formula for adding up the Hours for the Job & Payroll Category is:
=SUMIFS('Job Hrs Raw Data'!$I$2:$I$200000,'Job Hrs Raw Data'!$C$2:$C$200000,"1.5x Overtime",'Job Hrs Raw Data'!$D$2:$D$200000,$D$35)
I now want to edit it so that it works between 2 dates ie. 01.05.2021 to 31.05.21 & gives the summary of hours. This is my Raw Data below.
Shirley 1.5x Overtime 6151 13/05/2021 0.25 Walker Fab/Weld 6195 13/05/2021 8.00 Wormer Fab/Weld 6164 13/05/2021 5.00 Wormer Leave Without Pay 13/05/2021 3.00 Clarke Fab/Weld 6164 14/05/2021 6.00 Elias Fab/Weld 6164 14/05/2021 6.00 Ham Fab/Weld 6164 14/05/2021 6.00 Levi Site Work/Installation 6151 14/05/2021
6.00
This is my summary of hours.
PRODUCTIVE 1.5 x Overtime Leading Hand #VALUE! 1.5x Overtime $ 71.50 2x Overtime $ - Allowance - Meal $ - Base Hourly Wages $ - Del/Load/Unload $ - Fab/Weld $ 2,066.25 Leading Hand Higher Duties $ - QA/Workshop Duties $ - Site Work/Installation $ - Sunday Travel Time $ - Tech/Training $ 5.00 Travel Time $ - Unpaid Job Hours $ - Total Hours #VALUE!
I have set up the date paremeters as
Do not touch dates Start End Use 1/06/2021 30/06/2021 Do not touch dates, they have formula's Period Start End January. 2021 1/01/2021 31/01/2021 February. 2021 1/02/2021 28/02/2021 March. 2021 1/03/2021 31/03/2021 April. 2021 1/04/2021 30/04/2021 May. 2021 1/05/2021 31/05/2021 June. 2021 1/06/2021 30/06/2021
Just needing some assistance to correct the #value! error, if anyone can assist me.
Thanking you in advance.
Bronski