• Status: Solved
• Priority: Medium
• Security: Public
• Views: 247

# formulas for timesheet analyses

I have timesheet data collected from a fingerprint reader and have organized it in a pivot table on sheet 2 of the attached file.

I would like to get formulas in columns BA to BE for

Total hours worked
No of days counted
Average hours worked on each day
No of days worked less than 8 hours

whenever a time in as well as a time out is recorded.

In addition I would like to have a count of days present whenever there is at least one of time in and time out recorded.
Attendance.xls
0
Saqib Husain, Syed
• 2
2 Solutions

Commented:
ssaqibh,

You time log data doesn't seem to be completed. Most people don't have both In and Out time. That creates a big problem in calculating the time.
0

Commented:
There was no "Out" column that corresponded to the "In" of column AZ, so I inserted an extra column before your requested formulas.

I used offset ranges along with a test for even/odd column numbers to test for times being reported for both "In" and "Out". This forced me into a SUMPRODUCT formula.

Total hours worked
=SUMPRODUCT(\$E6:\$BA6*ISODD(COLUMN(\$E6:\$BA6))*(\$D6:\$AZ6<>"")*(\$E6:\$BA6<>""))-SUMPRODUCT(\$D6:\$AZ6*ISEVEN(COLUMN(\$D6:\$AZ6))*(\$D6:\$AZ6<>"")*(\$E6:\$BA6<>""))

Number of days worked
=SUMPRODUCT((\$D6:\$AZ6<>"")*(\$E6:\$BA6<>"")*ISEVEN(COLUMN(\$D6:\$AZ6)))

Average number of hours worked
=BB6/BC6

Number of days worked less than 8 hours
=SUMPRODUCT((\$D6:\$AZ6<>"")*(\$E6:\$BA6<>"")*((\$E6:\$BA6-\$D6:\$AZ6)<8/24))

Number of days with time reported
=COLUMNS(\$D6:\$BA6)/2-SUMPRODUCT((\$D6:\$AZ6="")*(\$E6:\$BA6="")*ISEVEN(COLUMN(\$D6:\$AZ6)))DUCT((\$D6:\$AZ6<>"")*(\$E6:\$BA6<>"")*((\$E6:\$BA6-\$D6:\$AZ6)<8/24))/BC6
AttendanceQ28286392.xls
0

Commented:
My formula for number of days worked less than 8 hours was wrong. It needed to include a test for even column number.
=SUMPRODUCT((\$D6:\$AZ6<>"")*(\$E6:\$BA6<>"")*ISEVEN(COLUMN(\$D6:\$AZ6))*((\$E6:\$BA6-\$D6:\$AZ6)<8/24))
0

EngineerAuthor Commented:
Great, thanks

Saqib
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.