Excel Timesheet - Counting Time

  GlasgowMary 22:39 20 Jun 03
Locked

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
TOTAL 48.50

I'd be grateful if anyone has a solution to this problem.

Thanks,
Mary

  VoG® 22:45 20 Jun 03

Sorry but your posted data is knackered by this site. Does this help? click here

If not feel free to e-mail me (click my envelope) and I'll do my best.

  GlasgowMary 22:58 20 Jun 03

The table on my posting is no longer a table!

Explanation:

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.
Mary

  jazzypop 23:06 20 Jun 03

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.

  Leeroy1988 02:01 21 Jun 03

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.

  Megatyte 02:29 21 Jun 03

ref

  GlasgowMary 22:53 21 Jun 03

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.

Mary

  VoG® 22:57 21 Jun 03

Yes the formatting, exactly as I posted click here

??!!??

  GlasgowMary 00:54 22 Jun 03

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

TOTAL 37: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.

Mary

  jazzypop 01:36 22 Jun 03

"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?

  VoG® 09:26 22 Jun 03

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…