Excel Time Sheet

  LABMAN 22:48 19 Jan 09

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 09

Format the hrs worked cells as Custom hh:mm

  Switcher 23:09 19 Jan 09

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 09

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 09

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

  Switcher 23:41 19 Jan 09

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

  LABMAN 00:36 20 Jan 09

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 09

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 09

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

  hastelloy 07:13 20 Jan 09

This might helpclick here

  LABMAN 09:48 20 Jan 09

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.

Huawei P10 review

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

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now