Time card Spread sheet help

Hi

I have been given the job of checking the time cards at work and would like to set up a spreadsheet to enable me to keep a record of them on a weekly basis per staff member until the payrun at month end. I can manage a basic spreadsheet but i am having difficulty in getting a suitable formula to enable me to calculate the hours worked as the time clock we use is a 100 minute one.

For example:

in 2013 mar 25 08.57
out 2013 mar 25 16.93

I am using Excel 2007 and when i try to set up the formula with a time it inserts a date as well and all the formulas i have tried have been errors due to the date aspect.

Hope you can help

Sue

It would help if you posted an example workbook, which shows what data you have and what you want to achieve from it. The FAQ describes how to attach a workbook to your post (basically, click GO Advanced, then scroll down and click on Manage Attachments, then click on Add Files, then navigate to where the file is and double-click it, the click on Add Files, then Done, then Submit Post).

Hope this helps.

Pete

Hi

Thanks Pete_UK i have attached the spreadsheet i have been looking to use. Its pretty basic but i have made notes at the side as to what i would ideally like it to show. What i am looking to do is on a weekly basis input the figures as they appear on the time card and let Excel do all the hard work

If possible i would like it to show the actual time started and finished and then a sum of hours worked by day, week and finally month.

If any of this is not possible as is, if someone could give me a solution to make my life a bit easier it would be appreciated.

All i know about Excel is what i have learnt through usage so my knowledge base it pretty basic so apologise if i am asing for the impossible.

Newdoverman - thanks for your help ill tell the boss to change it and will strop with him until he stops laughing at me

Sue

check the attachment if this is what result you want.

Acerrhod that looks absolutely perfect - thank you very much, i would never have figured that out no matter how many formulas i messed around with.

Thank you again

Sue

your welcome

just another quick question, on week 3 date 13/04/13 the start time by job card is 10:06 but with the formula its turning the actual into 01:04 how do i tweek the formula for this?

Is it possible to round these times?

For example - Column C4 8.21AM round to 8.20AM E4 4.57PM round to 5.00PM
Column C6 8.18AM round to 8.20AM E6 4.58PM round to 5.00PM

Thanks again

Thanks again

I have been working on the original template you sent me as it rounds but it doesnt seem to like whole numbers eg 8.40 i have attached a sample of the spreadsheet as i have it running now and highlighted the area which seem to be errors. As you can see i have inserted a couple more columns to seperate out the contracted hours and the overtime. Not sure if there is a better way to show these.

Eventually i will have a overview sheet which will pull all of the total data into a single sheet showing the basic total hours, contracted hours, overtime hours and holiday etc per staff member.

Sue

Use this formula in D6:

=TEXT(INT(C6)&":"&ROUND(MOD(C6,1)*60,0),"HH:MM")

and copy down as appropriate. Similarly, use this one in F6:

=TEXT(INT(E6)&":"&ROUND(MOD(E6,1)*60,0),"HH:MM")

and this one in H6:

=(F6-D6-G6)*24

Note that these give you the result in decimal hours, so 8.67 hours (in H9) means 8 and 2/3rds hours, or 8 hours and 40 minutes. If you want it to display as 8:40 then remove the *24 from the formula and format the cell as h:mm (but then you would have to adjust the formula in column J).

Hope this helps.

Pete

i modified the formula check the attachment

you may also use a formula for the holiday:
=IF(C6="HOLIDAY",8,((F6-D6-G6)-INT(F6-D6-G6))*24)
modified the column H formula to the above

Thanks ... Again i will check it out asap

Could i trouble you to look at this one for me .. then i promise to shut up

This is a second clock card we use which is a normal 60 minute one. I have set it up ect and the formula i have used appears to work as i want it to except when the actual worked hours are less then the contracted work hours.

I have also put in the overview sheet that i was hoping to use to pull all the data together which will incorporate both types of clock cards.

Can you let me know if you see any problems with it working, i dont think it will be a problem as its quite basic.

Sue