IF Statement Trouble with DATE and TIME - Employee Attendance

Welcome to the forum.

The 'full date and time' problem is easy to solve. Dates and times in Excel are just numbers. The dates are whole numbers starting from 1 Jan 1900 which is '1' - today, 24th Sep 18, is 43367. Times are fractions of the day: 0.5 = noon, 0.25 = 6am, etc. Hence the first date/time in your sample, 5:30am on 17th Sep 18, is 43360.2291666667. If you want to work with just the time, then you need to subtract the date part of this - the part before the decimal point - which you do by subtracting the integer.
So you can get just the time from C2 by using:
=C2-INT(C2)
Initially that will show just a number (0.229166667) so format the result as Time to see 5:30am.
You can then do your calculations based on just the times.

Hopefully that helps with part of what you need.


However, I can't see exactly where you want to do the calculations or where you have information about unexcused absences or no-shows? You say the sample is what you get from a timesheet report. How do you know if the absence of any clock-in/out times for a day is because the person wasn't planned to be in or because he/she didn't turn up? I assume this will need to be noted in another column?

Can you upload an example of roughly what you'd like to have as your final results? Mock them up manually if necessary, showing where you want to have results calculated by formulae. You need to include enough data to show all the variables you expect to encounter (for example, a line showing a tardy, a line showing a leave early, etc, etc) but you don't need hundreds of lines. If some of the data is being imported (clock-in/out for example) but other data is being entered manually, note this so that we can see what is changeable and what isn't.

Try these, in L2 and M2 dragged down, for your Tardy and Leave Early columns:

Formula:

=IF(C2="","",IF(C2-INT(C2)<=6/24,"n/a",IF(C2-INT(C2)>=6/24+1/24,0.5,0.25)))

Formula:

=IF(D2="","",IF(D2-INT(D2)>=16.5/24,"n/a",IF(D2-INT(D2)<=16.5/24-1/24,0.5,0.25)))

They will return blank if there is no date/time in columns C / D. That's the first part of each formula, in blue.
If there is a date/time, they will return 'n/a' if the clock-in time is before/at 6am / the clock-out time is at/after 4:30pm. 6am is 6/24, 4:30pm is 16.5/24. That's the second part of each formula, in green.
If the time is more than/equal to one hour before 6am / after 4:30pm, they will return 0.5. That's the third parts, in red.
If none of those conditions are met, the only possibility is that the time is within an hour of 6am / 4:30pm, so the result is 0.25. That's the last parts, in black.

There's one problem with the formulae - they don't account for someone leaving work and coming back on the same day. For example, on rows 144 and 145 of your sample file, the worker clocks-out at 4:31pm then clocks-in again at 4:32pm. The formula generates a 'tardy' of 0.5 for this second clock-in. Similarly on rows 167/168 there are both a 'leave early' 0.5 and 'tardy' 0.5 generated. It would be possible to amend the formula to look for just the first clock-in / last clock-out of each day, but it will make it a bit messier, so do you want that, or are you happy with it like this? If the latter, you could add some Conditional Formatting to highlight when there are two or more clock-ins/outs on one day.


Does that help?

Thank you for the feedback and the rep.

As you've only recently joined, you may not know that you can mark the thread as Solved if your original question has been taken care of so others know there's an answer here. Instructions to do it are in my sig - if you could take a moment to do it, that would be appreciated. Thanks.

Originally Posted by jclaborn

Quick question that just popped up, if a employee clocks in after lunch time, 12:00pm or later the employee gets .75 points.. How do would we add this to the formula to computer the .75 points for a noon + clock in?

Add another condition, like this (new part shown in red, note the one more bracket at the end too):

Formula:

=IF(C2="","",IF(C2-INT(C2)<=6/24,"n/a",IF(C2-INT(C2)>=12/24,0.75,IF(C2-INT(C2)>=6/24+1/24,0.5,0.25))))

It has come to our attention you have violated Rule 8 of our Forum RULES. Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.