Help counting how many time fall before and after mid day

[mbaughuk]

Good morning

I need to work out how many guys work both prior to mid day and how many work after

so in column M it will show at the bottom a total number of guys working the morning and how many in the afternoon

can anyone help

thanks

I have attached so you can see what I am in need of I have tried a few formulas but cant find anything that works

sorry not that good with Excel

thanks for any helpTest Planner.xlsx

[amit.wilson]

Hello M,

try using this formula:

Please Login or Register  to view this content.

"0.5" is 12 noon. So this will count the number of people working before 12 noon.

But for this to work, the time in column M must be in time format i.e. hh:mm:ss. Right now, your M column is in text format. And that won't work in the formula.

Also not sure what p.b. is.

Cheers

[mbaughuk]

Many thanks for this

the PB is something that we are going to stop using as of next week we are in the process of taking it all out

I cannot change them all to 0.5 as we need to know what time they are due to start work I have used you your code and it returns a 0 result not sure what I am doing wrong sorry

thanks

[amit.wilson]

M,

you don't have to change anything to 0.5

Change the format of the time in column M.

For example, in M3, you have "18:00". This is formatted as 'text'.

Change the format to 'time'.

As long as the data is in text format, my formula will return 0.

See attached.

Cheers

[mbaughuk]

Many thanks for this

can you let me know how to close this thread now

again many thanks for your help