# Excel time calculations

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:34 PM GMT

## Question

Hi I am having a few problems with some time calculations I am making incan you help. I am trying to capture a series start and stop times between the times stipulated starting at B20 and Finishing at C20. I am using the formula B20=20:00 C20=23:59 B3 and B4 are only a single demo of a series. I am adding B5 to C5 etc. B3=22:00 B4=23:00 B5=5

IF(AND(B20<B3,C20>B4),B5,0) B5 happily gives the answer 5, GREAT!

Now if C20=08:00 (through midnight) the problems start. If I use the formula below and change B4 to a time less than 08:00 but through midnight it picks that up, great, but if B4 is greater than 08:00 it also picks that up as well, NOT GOOD!! I only want B5 that happens between B20 and C20 IF(AND(B20<B3,C20+24>B4),B5,0) Also If I use the formula below and the formula will pick up all the B3 no matter what time it is providing C20>B4 agrees, NOT GOOD EITHER!!! IF(AND(B20<B3+24,C20>B4),B5,0)

Excel must have a method of doing this properly. Can you help? Thanks Eric