How Many Times an Hour Occurs Between Two Dates

[Jambonie]

Hello,

I have a sheet that is set up with 2 columns that have dates and times.

E.g. Col A has 10/1/10 17:08 and column B has 10/5/10 7:35.

I am wondering if anyone knows of a way to calculate how many times a particular hour occurs between those two times. For example, in the time range above, 11:00 would occur 3 times.

Thanks for your help!

[donjjjordan]

can you send a copy of your excel problem?

[Cutter]

Try this (not tested other than your sample)

=IF(HOUR(C2)>=HOUR(A2),1,0)+IF(HOUR(C2)<=HOUR(B2),1,0)+INT(B2)-INT(A2)-1

where first date/time is in A2, second date/time is in B2 and hour to check is in C2

Use this version if you want a result of 3 using the same dates/times but an hour of 17:00

=IF(HOUR(C2)<=HOUR(A2),0,1)+IF(HOUR(C2)<=HOUR(B2),1,0)+INT(B2)-INT(A2)-1
or maybe even
=IF(OR(HOUR(C2)>HOUR(A2),TIME(HOUR(A2),MINUTE(A2),0)=0+C2),1,0)+IF(HOUR(C2)<=HOUR(B2),1,0)+INT(B2)-INT(A2)-1

it depends on how you want to treat the hour on the start date.

[Jambonie]

Thanks Cutter, the first one worked perfectly for me!

[Cutter]

You're welcome. Don't forget to mark your thread as SOLVED (see FAQ at top of page for directions if needed).