Finding values between certain times

[kethyar]

Is there a way to find certain values between certain times? I would like to put this log all on one sheet, but i cannot figure out how to tell it to look for two certain values between, say 7am to 3pm. My time columns are formatted by Time, h:mm AM/PM. I have the other section of the formula figured out (find the two values) but I just cannot figure out between times. I have used < > but it doesnot seem to work on time values. Right now, the three time shifts I am using are broken up onto three separate pages, but I would like it to be all on one.

[davesexcel]

Is there a way to find certain values between certain times? I would like to put this log all on one sheet, but i cannot figure out how to tell it to look for two certain values between, say 7am to 3pm. My time columns are formatted by Time, h:mm AM/PM. I have the other section of the formula figured out (find the two values) but I just cannot figure out between times. I have used < > but it doesnot seem to work on time values. Right now, the three time shifts I am using are broken up onto three separate pages, but I would like it to be all on one.

Here's a formula
=SUMIF('Master Sheet'!B:B,"<="&A3,'Master Sheet'!C:C)-SUMIF('Master Sheet'!B:B,"<"&A2,'Master Sheet'!C:C)
Here's an example workbook useing the formula:
Get Values Between Times.zip

Change the times, the values will be added up from the "Master Sheet"

[kethyar]

i think i understand what's going on in your formula, but i dont think its exactly what i need. basically i have columns:

Time In | Time Out | HK/ENG | P/NP


and i want to be able to calculate the # of HK's and P's between 7am and 3pm, then again between 3pm to 11pm, and 11pm to 7am. i have the formula to calculate the HK's and P's, (and HK's and NP,s etc etc):

=SUMPRODUCT(('7AM-3PM'!H8:H50="HK")*('7AM-3PM'!L8:L50="P"))

is there a way to calculate using the above formula or place that in another formula that calculates these #'s between those certain shift times?