Thread: formula and computation for timesheet with workschedule

Am working on my time sheet. The problem is I can't compute for the tardiness and under time. Also I have to consider the working schedule in order to come up with the correct computation for it. sample below is the correct computation:

work schedule: Monday to Friday 8:30am to 5:30pm

time in time out tardy under time
employee name: 08:45 AM 04:30 PM .15 1.00

Because of the work schedule I can't come up with the correct result.

Thanks in advance. any help will be appreciated.

Hi

Have a look at the attached.

Cheers, Rob.

If a2 is time in and B2 is Time out , use below for tardiness and under time

=IF(A2-"8:0">0,A2-"8:30",TIME(0,0,0))

=IF("17:30"-B2>0,"17:30"-B2,TIME(0,0,0))

More clarifications wel come.

Hi Thanks for the assistance. Please re-attach the file. I tried using this formula for tardiness =if(AND(A2<>"",A2>TIME(8,30,0)),A2-TIME(8,30,0),"") and it works. but for the under time the result is 0.00. I also try to revise the formula same with the tardiness-it almost got it correct but it has to consider the work schedule of 8:30 AM (time in) 5:30 PM (time out).Is it correct that I should create another sheet ( sheet 2 stating the work schedule) then the main formula will be in sheet 1)? If this possibility is correct what formula must be established? Thanks in advance.

Apologies - forgot to attach!

A lot of the problem is in the formatting. Excel has to know that the input value is a time, and the output needs to be formatted in h:m to make sense. The actual formulae are fairly easy.

Hope this helps.

Cheers, Rob.

Thanks Rob I got it right! Can I ask another one? Now that I got it, Base on the established formula is it possible the excel could identify the week ends (i.e. Saturdays and Sundays) 'coz week ends is no work schedule? and under the column of tardy (A2) and under time (B2), what if there will b no time entry/ies can it be resulted to 0:00?

Thanks again and more power!

Hi

This is all possible.

Excel can determine if the day is a weekend using the WEEKDAY function, e.g. =WEEKDAY(A1). 1 is a Sunday, 7 is a Saturday.

Refer to the attached of all this included.

Regards, Rob.