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

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
Asked:
Saqib Husain, Syed
  • 2
2 Solutions
 
Harry LeeCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
Saqib Husain, SyedEngineerAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now