Counting the number of hours within weekdays.

[rcdiago]

Hello, Please help! I believe I figured out how to obtain the number of entries for the day of the week =SUMPRODUCT(--(WEEKDAY(C$2:C$23)=1)) for Sunday and so on....

I also need to know how many entries occur during that weekday at a certain time. For example, from the data I need to know how many entries occurred on a Monday at 9 am.

Attached is sample data due to privacy.

Thanks in advance.

Robert

[samba_ravi]

Try the below for total for all weekday at a specific time
=sumproduct((weekday(c2:c23,2)<=5)*(mod(c2:c23,1)=time(9,0,0)))

for particular week day
for monday
=sumproduct((weekday(c2:c23,2)=1)*(mod(c2:c23,1)=time(9,0,0)))
for tuesday
=sumproduct((weekday(c2:c23,2)=2)*(mod(c2:c23,1)=time(9,0,0)))
etc.,

[oeldere]

With an pivot table.

See the attached file.

[rcdiago]

Hello,

Thank you so much for your reply.

To be clear, the formula below I only change the number in bold for the days of the week?

=sumproduct((weekday(c2:c23,2)=1)*(mod(c2:c23,1)=time(9,0,0)))

Is it posible to have the formula show a range of time? Such as 9:00 am to 9:59am?

Thanks

[oeldere]

Please Login or Register  to view this content.

The anwer on that question is YES.

For the second question,
1) make a helpcolumn to determine the time.
2) make a helpcolumn to determine the range (9:00 am to 9:59am).
3) After that you can use pivot table or sumproduct.

For 2) I would make a table with the excpected ranges and use VLookup to find the description (in that table).

[jhren]

To show a range of time from 9:00 AM to just before 10:00 AM, change...

(mod(c2:c23,1)=time(9,0,0))

...to...

(INT(mod(c2:c23,1)*24)/24=time(9,0,0))

[rcdiago]

Hello,

Do the formulas below indicate the correct day of the week?

monday =SUMPRODUCT((WEEKDAY($L$2:$L$2162,2)=1)*(INT(MOD($L$2:$L$2162,1)*24)/24=TIME(7,0,0)))
.....
sunday =SUMPRODUCT((WEEKDAY($L$2:$L$2162,2)=7)*(INT(MOD($L$2:$L$2162,1)*24)/24=TIME(7,0,0)))

Thanks!

Robert

[jhren]

Yes.......

[rcdiago]

thank you all so much!!! I really appreciate your help.

[daddylonglegs]

If your time range is a whole hour then it might be easier to use HOUR function, e.g. for Sunday between 07:00 and 07:59:59 inclusive

=SUMPRODUCT((WEEKDAY(C2:C23)=1)*(HOUR(C2:C23)=7))