Excel time calculations

QuestionEdit

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

AnswerEdit

Working with time ONLY (without date) is like working with decimals ONLY, ignoring the integer, like 3.75 would be greater than 12.2 because 75 is greater than 2. You really need to include the date in the cell, but you can FORMAT it to show only the time portion. OR... You can test to see if C20<B20, then add 1 (day) before you subtract the times: =IF(B20>C20,IF(AND(B20<B3,C20+1>B4),B5,0),IF(AND(B20<B3,C20>B4),B5,0)) or simpler: =IF(AND(B20<B3,(C20+(B20>C20))>B4),B5,0)

HTH