# Excel timesheet formatting

Microsoft Excel 2003 (full product)
September 9, 2009 at 23:38:45
Specs: Windows XP
 Some timesheets I have created are easy others not so. One group various start & finish times and within that time frame many tasks costed to different areas. The Start/finish times i have sorted out with =(G8-G6-TIME(0,30,0). When they work on a particular job for a couple of hours and minutes they record that on a particular day(column A eg 2.36hrs-job 1, 3hrs job 2, 3hrs job 3 etc till they have worked 8.52hrs ie 6:38am to 16:00 and will accrue .16hrs to a TIL(time in lieu day). I have used a formula to add all the cells going across the time sheet for a particular job, so Monday( job 1) 2hrs + tues (job 1) 2.36hrs + wed (job 1) 4hrs etc. These calculate into column X(right off the timesheet =SUM(A13:G13) and then hidden that column)then in the total cell at the end of the week back in column H used this string =INT(X13)+(X13-INT(X13))/0.6 to convert hours and minutes into decimal for entry into our payroll system. Now my problem is that when i add small minutes values ie in cell A13 i input .16 or -.16 and contiue through to cell G13 the decimal auto calculation is out. It works with larger values ie 2.36 in each day no problems, but not with the smaller values. Particularly with the negative values. I am assuming that the Integer string is only working on a positive value and not recognising negatives because when I input -.16 the autocalc comes out at .40 when in fact it should be -.27 also it will only calculate 6 of the 7 columns as it reverses one of the values. It does not matter which order you input the columns ie A13, B13, D13, E13, F13, then when I input either G13 or C13 it goes backwards by that value then forwards again with the last cell adding the last value in. I have formatted all the cells to numbers. I really hope this makes sense. Your help would be appreciatedThanks

See More: Excel timesheet formatting

#1
September 10, 2009 at 08:28:44
 Sorry about the posting - I'm new to this - hope this is more readableHello 650PeteTimes in Excel appear difficult, but if you ensure that all data entry follows a time format that Excel recognizes, you will be able to add and subtract times without any need to use INT or TIMEEnter start and finish times as hh:mm, for example 07:30Format all the cells used for time entry and time calculations with "hh:mm"The duration of work is just Finish-Start```Start time in A2 07:30 Finish time in B2 11:10 Duration in C2 has the formula =B2-A2 and if formatted "hh:mm" will show 3:40, ie 3 hours and 40 minutes ```If staff may work past midnight use the following formula=IF(B2

Report •

#2
September 10, 2009 at 22:35:05
 Thanks Humar. I will play with your idea for a bit and see what happens over the next couple of pays. Thanks for your input.

Report •

Related Solutions