Pretty simple time problem

[drummerkid]

Hey guys,

First time poster and an idiot at excel. I've done a bit of programming in python/java but that just seems to confuse me when it comes to the syntax of excel functions.

Right, what i'm planning to do is create a spreadsheet that compares the clock in time for an employee against their rostered clock in time and flags any that are out by greater than 7 minutes, either way. I've got this working fine (Refer to conditional formatting in colums O and P).

The bit im struggling with as as follows:

I want column Q to display the correct amount of hours worked ONLY if the automatic clock in time is within 7 minutes either side of the rostered clock in time.

By automatic clock in time i mean column E 'Roundstart' and by rostered clock in time i mean column M 'Start'.

As you can see in Q7, it works if the automatic clock in time and rostered clock in time are EXACTLY the same but i need the 7 minute leeway each way (early or late)..

Any tips would be greatly appreciated...

Thanks,

Drummerkid

[6StringJazzer]

Use this formula in Q3 and copy down:

=IF(AND(ABS(M3-C3)<7/(24*60),ABS(N3-D3)<7/(24*60)),I3,"")

[drummerkid]

Hey,

Thanks alot, works a charm!

If someone can mark this as solved, that'd be great - it might be of some use for people doing the same sort of thing with employee timesheets.

Cheers,

Drummerkid

[ConneXionLost]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save