Roster

can some 1 please help

I am working on a work roster sheet and I need to work out how many people we need for the number of hours we have.

So say we had 1350 hours work and everyone works 35 hours I thought i could just div 1350 by 35 but it comes up with an answer of 1.41 !!!

Is my problem caused by me trying to div a number by a time value ?

Any help would be cool

Thanks

Hi, just an attempt.

1.41 probably means 1.41 pm: you should format the cell as number. More or less is 38,5.

Hope it helps.

an example worksheet would be helpful

Still can't get it to work.
tried the above but I still get 1.41

Have uploaded an example

Thanks for the help

Hi, try to change in E10 the formula in

=E9/35*24

Regards

Hi
Currently in your format excel is converting your time value 1183:50 to a decimal of 49.326 and then dividing this by 35 which gives you your 1.41.
To overcome this you can convert your initial time value to decimal without the minutes. eg. A4 = 158, B4 = 175, C4=165, E8 =250 etc then your total hours for the week will be 1183.50 in decimal and then you can divide this figure by 35 to get your answer.

Great Thanks guys for putting me right on this just 1 other question

I am using the follow code to prefix 2 letters in a cell when a number is typed in
but work now what to have a and b shifts after the duty numbers.
When I type in the a or b the code will not pick it up and add the eh part as the cell is not a number.
Any ideas how I could change it to do that.

Thanks


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False

For Each cell In Target
If Not Intersect(cell, Range("B6:B50,D6:D50,F6:F50,H6:H50,J6:J50,L6:L50,N6:N50,B57:B58,D57:D58,F57:F58,H57:H58,J57:J58,L57:L58,N57:N58")) Is Nothing Then _
If IsNumeric(cell) Then cell = "EH" & cell.Value
Next cell

Sorry No idea what code tags are !!!!

Hello Sprinter1801,

If you select all of the code and then click on the # symbol in the list of formatting options above then you get "code tags" and it looks like this:

Please Login or Register  to view this content.

One of this forum's rules is that all code should be wrapped in code tags