Using Excel Worksheet to post Time IN/OUT, add up DAILY Hours, Weekly Totals, and BI-WEEKLY Totals for those on two week pay periods.   Okay maybe I ma an idiot, but it has taken me years to figure this one out and I finally did it.  I have read posting after posting on 'how to' but most only solved half my problem, or still required me to do manual math after the fact.  Now I have found the formula that allows me to input the time in, time out for lunch, time back in, and time departed or the day, THEN it give me a total for the day and adds a total for the week.  AND if that isn't enough, I can allow it to total two weeks worth too.

I have found post that tell me how to calculate those hours into dollars using simple formulas but I have not gone into that yet.  So here is how to make it happen.  Disclaimer.. 1) There may be an easier way but I have not found it, other than buying software. 2) You can change the headers, text, or timeframes accordingly, and insert more in/out sub sections depending on your work day... this is the basic In for 8-12 hours with a lunch in the middle. 3) You can even add headers using insert.  Obviously if you add headers you will have to change the LETTERS or line numbers to correspond with where YOU start.  I started at A1. (Column A Line 1).

A1-7 Right Click, Format Cell, General (or text).  In each box respectively I input Monday, Tuesday, Wednesday, etc through Sunday. Your work week may start on Wed, so modify accordingly.

Example:  A1 Monday; A2 Tuesday; etc.

B1 and C1 Right Click, Format Cell, Time, 13:30 (important note here.. you or your staff MUST use military time for the math to work later.  Midnight is 00:00, noon is 12:00, and 1 pm is 13:00, etc... through 23:59.  Also you must input the colon you can not just put in the time number... so 1315 is no good, 13:15 is okay.  The B column will be used for time in (1) and C column for time out (1).  Columns G and H are duplicates of B and C and used for a return time if the employee left for lunch and a final departure 'punch out'

D1 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell. =C1-B1   (Important note, you must use the 37:30:55 time format for the hours to add up over 24, or to the forty hour plus work week later.  I know it does not make sense that the seconds are never input in, but trust me it is the only way I could get this to work.

E1 Right Click, Format Cell, Date, 3/14 (or your favorite format)  You are going to have to come back to this box later and clean up the daily count formula, but I will address that at the very end, because you may already no how to get the date to change automatically, after inserting the start date. But if not, I will show you at the end.

F1 through J1 simply copy and paste A1-E1, they are the repeat and used for second shift or returns from lunch.

K1 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell. =D1+I1   (This will calculate the daily total for both shifts.)

Now Highlight and Copy A1 through K1 and paste to A2 through K7.  This will set up the same pattern for input for each day of the week.

A8, B8, C8, E8, F8, G8, H8, and J8 are all blank boxes.  If you want, you can input into A8, E8, and J8 the word Week One (two, three etc).

D8 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell.  =SUM(D1:D7)   This will calculate the Weekly Total time for this shift only.

J8 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell.  =SUM(I1:I7)   This will calculate the Weekly Total time for this shift only, if any.

K8 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell.  =SUM(D8:I8)   This will calculate the Weekly Total for both shifts, or the week total.  On a five day work week this would be about 40 plus hours.

Now the easy part....

Highlight A1 through K8, COPY (Control C), and then PASTE (SHIFT INSERT or CONTROL V) to cell A9, which will paste through K16 the entire process from week 1 to week two.

ALMOST DONE...

L16 Right Click, Format Cell, Time, 37:30:55 THEN insert the following formula into the cell.  =SUM(K16:K8)   This will calculate the TWO Week Total for ALL shifts.  (Note you can repeat this process if you are on a four week or monthly cycle, simple change your patterns or boxes to be calculated.)

Two steps left....

Copy (Highlight) A1 through K16 and PASTE to A17, and every empty box after the paste is complete until you have all 52 weeks accounted for. 

 LAST STEP.. No really it is...  FIXING THE DATES...

In cell E2 insert the following formula.  =E1+1   (Note: repeat this action for J Column)

Copy E2 to E3 through E7.  (Note: repeat this action for J Column)

In cell E9 insert the following formula. =E7+1  (Note: repeat this action for J Column)

In cell E10 insert the following formula.  =E9+1  (Note: repeat this action for J Column)

Copy E10 to E11 through E15.  (Note: repeat this action for J Column)

You will now be able to insert a start date into box E1 and the system will automatically carry the dates to the bottom of the column, and depending on how many times you pasted, you will have 1, 2 3 or more years worth of tracking.  Save sheets by employee name if you want.  Just copy all to next sheet.

HOPE THIS HELPS.  Yes you can then look at the message board and see how to use the totals for calculating pay, using those lessons.  Give me a break it took me years to figure this part.

Need more help... Contact me at raymanfredi@allstate.com