Time Values - counting ranges

[Monkeyboy126]

Hi All,

Attached is a spreadsheet I'm working on which is for a Service Desk resources rota. The staff work numerous shifts but I'm interested in counting (on a daily basis) number of people starting before 10.00 and number of people working from 16:00 onwards (columns L and M).

Ideally the formula will ignore null fields.

As always, any help greatly appreciated and I hope I've explained it well enough. I'm using Excel 2010 if that helps.

many thanks

 

[RivetHed]

Can't see your spreadsheet but do you just want a formula to stick in an extra column to make the counting easier or a big array formula to calculate the whole thing in one go?

If the former then something like this should give you a 1 or a 0 for those who arrived early so you can run a count:

=IF(ISBLANK(A2),"",IF(TEXT(A2,"hh")*1<10,1,0))

 

[GlennUK]

RivetHed,

surely TEXT(A2,"hh")*1 is the same as using the HOUR function, like:
=HOUR(A2)



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[RivetHed]

Glenn, correct.

Completely forgot Excel had that function, that simplifies the formula a bit.

 

[Monkeyboy126]

Apologies, try this link to Spreadsheet

http://www.mediafire.com/?uyt6fzep75mfzka

 

[SkipVought]

hi,

1. Your file reference seems incomplete.

2. Even if it were valid, many people, like myself, cannot access during the day, due to company security restrictions.

3. It would be better to post a columnar example that is representative of your issue, using [blue]Process TGML[/blue] tags to present your data in an understandable fashion.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

L5 =COUNTIF(B5:K5,"<0.42")

K5 =COUNTIF(B5:K5,">0.65")

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[xlhelp]

Please note: above does not take into account anyone starting between 10:00 and 16:00

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[Monkeyboy126]

xlhelp - This is exactly what I need and works very well.

Can I ask how the .42 and .65 relate to hours in the day?

many thanks

 

[SkipVought]

In 2 separate cells...
[tt]
=TIME(10,0,0)
=TIME(16,0,0)
[/tt]
and format GENERAL.

Observe the numeric values.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You could also code...
[tt]
=COUNTIF(B5:K5,"<"&TIME(10,0,0))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

... or you could do = 10/24 and = 16/24

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Monkeyboy126]

Got it working fab, thanks to all!

Cheers