help, schedule checking using sumproduct and the left function

[Helenrich80]

Hi,

I am trying add up the amount of times someone works between a certain times over the course of a month on a schedule.

For example how many time did they start between 5am and 9am so I have put in the below formula.

=SUMPRODUCT(--(AND((LEFT(B6:H6,2)>="05"),(LEFT(B6:H6,2)<"09"))))

However this returns a value error message on the range

If I change it to one cell range it works
=SUMPRODUCT(--(AND((LEFT(B6,2)>="05"),(LEFT(B6,2)<"09"))))
Clearly to do this over a full month for each day for each time range for several people will be very work heavy.

Can anyone help please??

 

[steve the fish]

AND doesnt produce arrays which is what you need here. Use multiplication. How are these times stored in the cell? Does LEFT(B6,2) produce "05" for example?

 

[Helenrich80]

So it looks like

A B C D
0530-1400 1100-1900 0800-1600 1700-0230

 

[steve the fish]

There must be more data? Place an example here of what you want to calculate. That example doesnt seem to correlate to your sumproduct formula.

 

[steve the fish]

Oh i get it now. Try:

=SUMPRODUCT((0+LEFT(A1:D1,4)>=500)*(0+LEFT(A1:D1,4)<=900))

 

[Helenrich80]

I could cry!! it works THANK YOU!!!