Formula for hours between two times

Hi,

I need formulas for Hours between two times.

Attached excel file with relevant details.

Pl. help.

thanks,
Nagesh.

Hi,

Something like this?

Hi,

in Column "H", I actually need the count (No. of vehicles between the respective hours).

Pl. provide the formula.

thanks,
Nagesh.

=SUMIFS($E$3:E$14,E$3:E$14,">00",$E$3:$E$14,",<=1") in H4

=SUMIFS($E$3:E$14,E$3:E$14,">1",$E$3:$E$14,"<=2") in H5 etc

=SUMIFS($E$3:E$14,E$3:E$14,">4") in H8

etc

I tried and got it. =(COUNTIFS(E$3:E$14,">0")-COUNTIF(E$3:E$14,">1"))

Hope this is correct.

Thanks.

Hi,

See the attached file

Hi John,

in H5 as per your formula getting answer 2 but as per my formula answer coming 1. Even H8 I am getting result 9 as per your formula but as per mine, it is 0 (nil).

Anyhow thanks.
Nagesh.

Hi cbatrody,

Even there is difference between mine and your result in H4. My answer is 0 and your 2.

=COUNTIFS($E$3:$E$14,">0",$E$3:$E$14,",<=1") etc

Originally Posted by nagesh.tvsr

Hi cbatrody,

Even there is difference between mine and your result in H4. My answer is 0 and your 2.

yes, there are 2 vehicles with "0" which are row 5 and row 13

If you want H4 to be "0", change the formula in H4 as following:

=COUNTIFS(E$3:E$14,">"&0,E$3:E$14,"<="&1,B$3:B$14,"<>"&"")

Hi cbatrody and John,

Now the results are matching with you both.

By the way, is there anything wrong with this formula? Because this looks to be simple.

=(COUNTIFS(E$3:E$14,">0")-COUNTIF(E$3:E$14,">1"))
=(COUNTIF(E$3:E$14,">=1")-COUNTIF(E$3:E$14,">2"))
=(COUNTIF(E$3:E$14,">=2")-COUNTIF(E$3:E$14,">3")) ...... and so on.

Anyhow thank you.

You can work with those formulae as well if you wish to, they are simple and gives the required output.