checking if two times are between certain hours

[Kmitchell]

This should be easier than I'm making it...

Start times all sit in column J (military time), and stop times sit in column K (also military time). The duration in minutes sits in column M.

I want to put in a formula that says if columns J and K are between the hours of 7:30am and 3:30pm, then grab the duration minutes sitting in column M, otherwise leave blank.

I get an error message when I enter in the below formula. Any suggestions?

=IF(AND(J2>=7:30,K2<=15:30),M2,"")

 

[barry houdini]

You need too use a slightly different syntax for the times to be recognised, try

=IF(AND(J2>="7:30"+0,K2<="15:30"+0),M2,"")

 

[Kmitchell]

Thanks - that wasn't quite accounting for all cases.

The below formula works. Now I'm just trying to get it work for instances where hours are after 3:30pm and before 7:30am.

IF(AND($J$3>TIMEVALUE("07:00"),$K$3<TIMEVALUE("15:30")),$M$3,"")

 

[barry houdini]

Your formula got cut off......

Not sure what you need to modify, if you still need some assistance can you give some examples?

 

[Kmitchell]

Sorry about that. I think I'm good now.

I used the below formula for all instances b/w 7am and 3:30pm.

IF(AND(J11>TIMEVALUE("07:00"),K11<TIMEVALUE("15:30")),M11,0)

My work around formula was just to take all those instances that had 0 minutes.

IF(N11=0,M11,IF(N11>1,""))