Sunday, April 5, 2015

yogi_Compute Row By Row If givenTime Falls Between startTime And endTime


                                           Google Spreadsheet   Post  #1936
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-05, 2015
question by Mark Agustin:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Mk2xMfM3s10
If time falls between two TIME (Crossing Midnight)
Hi,

Badly need help. I need to know if a certain time falls between two (2) TIME (that crosses midnight). It's goes like this:

B1: 23:00 (start)
C1: 8:00 (end)
D1: 7:00 (time given)

I want a result that says "YES" since 7:00 falls between the A1-B1. I made a nested if, but it doesnt seem to do the job:

(IF(D1="",0,IF(C1=D1,C1,IF(D1=0,C1,IF((C1>=D1)+(C1<=E1)+(E1<D1)=2,"YES","")))))

This results YES to all. Really need your help!
------------------------------------------------------------------