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 appreciated

See More: Excel timesheet formatting

Report •

September 10, 2009 at 08:28:44
Sorry about the posting - I'm new to this - hope this is more readable
Hello 650Pete

Times 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 TIME

Enter start and finish times as hh:mm, for example 07:30
Format 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
This adds 1 to the finish time when the finish time is 'earlier' than the start time, because the finish time is in the next day.

Excel's date/time serial number holds dates in the integer part of the serial number and times in the decimal part of the number.
For example 09 September 2009 is 40066
12 noon on 09 September 2009 will be 40066.5 i.e. half way through the day.

Only do your decimal conversion for input into the payroll system as a final step - don't mix decimal calculations and Excel's time serial numbers

If the final hours:minutes to be converted are in cell F2
put hours in G2 with this formula
and minutes as a decimal part of an hour in H2 with this formula =MINUTE(F2)/60

I know this doesn't answer your specific questions, but it will likely be easier to start from scratch just using Excel's time system,
(and program maintenance will be easier in the future).

By the way Excel's serial number does not handle negative time values. If you needed to have time debited, keep the value positive but label the cell or group of cells as debit or time owing or whatever and just subtract the positive number rather than trying to add a negative value.

You may want to consider adding data validation to input cells so that time entries are never greater than 1 (1=12 midnight).

Good Luck

Report •

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

Ask Question