Excel Time Sheet

  LABMAN 22:48 19 Jan 2009

Hi Folks,

I'm looking for some help in setting up a simple time sheet to total the hours satff work over the course of the week, using most of Excel's in built formats I can't get anything to work and it's a pain having to convert hours and minutes to a decimal to get it to work.

It's OK when a shift is 11Hrs 15Mins long and you can input 11.25 or 7.5 for a 7 1/2 hour shift but some are 9Hrs 37Mins or any variation in between as not all shifts are the same length, is there a simple formula to enter that would for instance add 5 shifts of 9hrs 37mins and give a result of 48hrs 5mins and not 46hrs 85mins.



  Switcher 23:05 19 Jan 2009

Format the hrs worked cells as Custom hh:mm

  Switcher 23:09 19 Jan 2009

Sorry to make it clearer Format the hours worked cells as Number>Custom>hh:mm You can then enter the time as 11:27 or 9:49 E.G. Should add up or multiply hh:mm as long as the formula cells are similarly formatted.

  LABMAN 23:28 19 Jan 2009

Hi Switcher,

I've just tried that, entered 5 cells 9:37 but when you add them up it only shows 00:05....


  Switcher 23:35 19 Jan 2009

Sorry, seems to reset hours to zero when it reaches 24. Will have another look

  Switcher 23:41 19 Jan 2009

OK, works if you format relevant cells as
Custom [h]:mm:ss

  LABMAN 00:36 20 Jan 2009

Thanks Switcher, that has done the trick it's a pity about it having to include the seconds but at least it's working for me.



  LABMAN 00:38 20 Jan 2009

Just to say Switcher I found out if you delete the seconds in the display box it only shows hours & minutes, perfect for what I want.


  Switcher 01:02 20 Jan 2009

Thanks LABMAN meant to check on the seconds bit but got distracted. Glad to know it solved your problem.

  hastelloy 07:13 20 Jan 2009

This might helpclick here

  LABMAN 09:48 20 Jan 2009

Hi marvin42,

Thanks for your additional input to my question, I was simply looking to be able to put hours and minutes into a spread sheet and add them up correctly.

Our relief staff who cover holidays & sickness etc work varying shifts lengths between 8 & 12 hours with 3.75 mins per hour for a break which can result in all sorts of odd shift lenghths and it's only the shift length we care about.

We have to try and give them a minimum of 32 hours work a week and a maximum of 48 and avaerage it out to as close to 150 hours over a 4 week period so Switchers formula worked a treat.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?