Best Black Friday Deals 2017
Does anyone know how to count time?
I've created an Excel Flexi-time timesheet where the total weekly hours worked can be up to 49 hours. Since counting anything over 24 hours resets the counter to zero then adds on the difference (giving a wrong total figure), I've had to convert the sum of the daily totals to decimal time by changing the total cell format to number and multiplying the sum by 24. This gives me the correct number of hours worked but is clumsy since all other time is 'real' and the total is decimal. See the table below.
Date In Out In Out TOTAL
14/7/03 8:00 12:30 13:00 18:30 10:00
15/7/03 8:00 12:30 13:00 18:30 10:00
16/7/03 8:00 12:30 13:00 18:30 10:00
17/7/03 8:00 12:30 13:00 18:30 10:00
18/7/03 8:00 12:30 13:00 17:00 8:30
I'd be grateful if anyone has a solution to this problem.
The table on my posting is no longer a table!
The first six words are column headers. The rest of the data is in groups of six, each group of six being the time entered in concecutive cells in the next row down.
At the end you have the decimal hours TOTAL 48.50. These are in columns 5 & 6.
Hope this helps.
If you want to force text to start on a new line (when posting to this forum), you need to hit Enter twice, not once.
I have used such a timesheet and it's all down to the formatting of the sum cells. I could send you the sheet but I can't attach a file to this posting. Basically, goto the totals cell, right click and select "Format Cell", then "custom", and then create a format for the "total" cell as "[h]:mm" - note the square brackets. If you want to give your e-mail address, I'll send you a sheet.
Thanks to LeRoy1988 - it works a treat.
I had complaints about having to use a colon when inputting time on the timesheet (9:30 instead of 9.30). I got over that by creating a hidden column and used the formula =(INT(D7)+(D7-INT(D7))*100/60)/24 that was on a previous (Excel-changing value to 'time') discussion thread.
I couldn't have done it without help. So thanks again.
Yes the formatting, exactly as I posted click here
I've nearly finished my timesheet, there are just two things that I'd like to do to finish it off.
My total hours figure format is 9:30. Since the user inputs 9.30 (my hidden column converts to 9:30. I use this hidden column to sum time), I'd like the TOTAL figure to look the same. See table:
Date In Out In Out TOTAL
16/6/03 9.00 13.00 14.00 17.30 7:30
17/6/03 10.00 14.00 15.00 18.30 7:30
18/6/03 11.00 15.00 16.00 19.30 7:30
19/6/03 12.00 16.00 17.00 20.30 7:30
20/6/03 13.00 17.00 18.00 21.30 7:30
Is there a formula that will convert the time format to look like a decimal?
Also, I'd like the timesheet to open up in full screen mode without column and row headers. Does anyone have some VBA code that will allow me to do this?
VoG® - You're absolutely right. In future I will take note when you post a reply.
jazzypop - Thanks for the advice about using the site. I'll be interested to see whether the table above keeps its shape.
"You can customize the default workbook or worksheet to not show column and row headings or to contain any other customizations. To do this, make any changes required. Choose File, Save As, change the Save As Type to Template, the File Name for a Workbook to Book.xlt or Sheet.xlt for a worksheet.
Change the Save In location to C:\Program Files\Microsoft Office\Office\XLSTART. Click Save."
Source - click here (I'm sure VoG® will be able to come up with a natty little VBA module that will be more elegant, though).
Not too sure about the 'decimal time' bit - do you mean you want 37 and a half hours to be displayed as 37.5 instead of 37:30?
As jazzypop says, the easiest way of achieving full screen without row and column headers is to use Tools/Options to make those changes then save the file. It is possible to do this in VBA as well but is usually unnecessary unless you want to disable other features as well; e.g. scrollbars.
If you want to display "37:30" as "37.3" use a custom format [h].mm or similar. If you want it to display as "37.5" that is a bit trickier.
This thread is now locked and can not be replied to.