0

I have a spreadsheet containing timekeeping data - hours worked, vacation leave, sick leave, etc.

Our policy is that all entries must be in increments of 0.25. Due to a technical glitch, we had to 'turn off' a setting that ensures entries are in 0.25 increments. Now, I need to find out if any of the entries are not in 0.25 increments.

Can anybody think of a formula that can search a row of data (for each employee on the spreadsheet) that will determine if any of the pay code entries within the row were not an increment of 0.25?

For example

  • one row might contain 74.5 hrs worked and 5.5 vacation hours; this is okay because both values are in increments of 0.25.
  • However, if someone had 74.5 hours worked and 4.87 hours of vacation, I need to find the 4.87 usage because it's not in an increment of 0.25.
  • 2
    =mod(A1,.25) - anything not 0 is off – Raystafarian Jan 4 '16 at 13:53
  • Thank you Raystafarian! I summed the row and then applied the 'mod' formula you provided. Out of 2,170 rows, I only had 3 rows where employees did not use increments of 0.25 for their timecard entries. And, this is what I needed to zero in on. Thanks!! -Annette – Annette Jan 4 '16 at 14:01
  • @Raystafarian, could be worth moving to an answer. If he does Annette, please do mark it as answered – MyDaftQuestions Jan 4 '16 at 14:25
1

You can use the mod function to return the remainder of the sum of the rows, identifying rows you need to check.

=Mod(A10,.25)

1

If you have more than 1 type of time reported per line, you can use

{=SUM(MOD(B3:D3,0.25))} 

to identify which line has an improper entry. (you must press CTRL+Shift+Enter after typing the formula to create the array formula)

This formula assume you have: EmployeeID in Col A, Regular Hours in Col B, Overtime Hours in Col C, and Paid Time Off in Col D.

If you have more columns, expand the range.

edit: Formula changed per Máté's suggestion.

  • Does it make sense to sum within mod? what about {=SUM(MOD(B3:D3,0.25))} – Máté Juhász Jan 4 '16 at 14:55
  • Doesn't (A + B + C) / .25 equal A/.25 + B/.25 + C/.25? Either formula will work. – B540Glenn Jan 4 '16 at 19:48
  • Mod is different than simple division, please check: (0.1+0.15) mod 0.25 vs 0.1 mod 0.25 + 0.15 mod 0.25 ! – Máté Juhász Jan 4 '16 at 19:51
  • Máté, I see your point. Make it an answer and I'll vote for it. – B540Glenn Jan 5 '16 at 16:51
  • It's your answer:), just correct it, my purpose was to help improving it. – Máté Juhász Jan 5 '16 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.