self calculating time sheet

**gerard_gonzales33** · 01-17-2012, 04:04 AM

I created a template of semi-monthly time sheet including overtime. I tried to make it automated (self calculating) using excel, but I lock of information in creating excel formulas. The things that I need to know are as follows:
1. How can I automatically input the dates or is there a formula in entering the dates that will recognize the week ends? (i.e. Saturdays and Sundays)
2. Formula for tardiness and under time (work schedule is 8:30 AM to 5:30 PM Mondays to Fridays).
3. Over time has a minimum of 1hr claim which starts at 6:00 PM.
4. In entering the time, can I use the hh:mm without affecting the formulas that will be established? ( 'coz every time I enter a time it always includes the seconds)

Am attaching my template as reference. ( I hid some of the columns for this is manually to be type).

Thanks

Gerard "the desperate timekeeper"

**JBeaucaire** · 01-17-2012, 04:46 AM

1) enter a date manually into A10
2) Formula added in A11: =A10+1
...then copied down.

3) Remove all color formatting from A10:A24 then apply a conditional formatting. Select A10:A24 then apply these settings:

FormulaIs: =OR(WEEKDAY(A10)=1, WEEKDAY(A10)=7)
Format...Font Color: Red

4) In H4 put the following formula:

="PAYROLL PERIOD: " & TEXT(A10, "mmmm d - ") & TEXT(A24, "mmmm dd, yyyy")

Not sure what else you want, not enough info. Feel free to add examples to your sample workbook of what you want help automating.

**e4excel** · 01-17-2012, 04:50 AM

Can you please mention some expected results in the SHeet to better understand the requirment..

Warm Regards
e4excel

**gerard_gonzales33** · 01-17-2012, 08:26 PM

Thanks for the response in my need. I already applied the formulas as you mention in the items #1 and #2, but in items #3 and #4, It seems I kinda lost, can you teach me step by step in inserting the formula and format? Thanks in advance.

**gerard_gonzales33** · 01-17-2012, 08:39 PM

Thanks for the response to my need.
My expected results is to have it automated such as:

1. Like when I input the time in as 9:00 AM and time out 5:30 PM (the work schedule is 8:30 AM to 4:30 PM) in cell H12 will result as .30 (as tardiness) and in cell I 12 will result to 1.00 ( as under time).

2. When entering the dates, Is there a way that it will be recognize as weekends (i.e. Saturday and Sunday)?

Thanks.

**kvsrinivasamurthy** · 01-18-2012, 06:44 AM

Pl see the attached file.Any clarifications you are welcome.

**gerard_gonzales33** · 01-18-2012, 10:00 PM

Hi there! thanks for the assistance. I got the formulas for the tardiness and overtime. Could you also help me in creating a formula for the part of under time? I tried it but the result is ####. What should I do?? thanks in advance.

**kvsrinivasamurthy** · 01-18-2012, 11:26 PM

Pl explain what do you mean by under time.

**gerard_gonzales33** · 01-19-2012, 02:19 AM

let say the work schedule of an employee is 8:30 AM to at 5:30 PM:
tardy= coming late at work while under time= time out or logs out earlier than the work schedule.
the employee logged in at 8:35am and logs out at 4:30 PM; tardiness will result to .05mins and with under time of 1.00hr

**gerard_gonzales33** · 01-19-2012, 03:41 AM

Hi everyone! my apologies, I would like rephrase all my queries re: the self calculating time sheet. I just realize that my queries are incomplete same with the attachment.

Here's my final queries for the self-computing time sheet:

needs to be automated:
1. computation for tardiness, under time and overtime (with grand total in per day and in semi- monthly).
2. Is it possible that upon entering the date, week ends will be recognized? (i.e. Saturdays and Sundays)
3. How about half days? will it also be computed?
4. In entering time is it also possible not to include the seconds? (HH:MM) only

Attached with is the sample semi-monthly time sheet I have.

Thank you for your time and effort in assisting me.

Gerard "the desperate timekeeper"