Time ranges

[DeCaelo]

I have 2 columns of dates, say 17/03/2011 09:08 and 17/03/2011 09:36.

I'm trying to figure out a formula that shows "ok" if the 2nd date is 30 minutes either side of the 1st date, but can't quite figure it out.

=IF(MINUTE(DATE2-DATE1)>30,"OK","NOT OK")

I've got so far as the above but this obviously doesn't factor in hours or days :s

Can someone help please?

 

[bryonwoods30]

when i did it I just used =date2-date 1, then change the format of the cell to [h]:mm:ss in custom. this will give you the exact number of minute. then just write your if statement based on that cell.

 

[DeCaelo]

I originally did try it that way, but had problems as date 2 could be earlier than date 1.

For info, It was solved with the following which calculates whether the absolute value between the 2 dates is greater than 30 minutes.

=IF(ABS(DATE2- DATE1)>TIMEVALUE("00:30");"FAIL";"OK"))

 

[DeCaelo]

I have a new problem though :s

I have 3 sets of dates/times,

DATE 1: 16/02/11 00:01
DATE 2: 00:50:00
DATE 3: 16/02/11 00:31

I'm struggling to find a formula that calculates whether DATE 3 is between dates 1 + 2, no doubt due to DATE 2 being just a time, rather than a date.

 

[DeCaelo]

bump

 

[shift-del]

I'm struggling to find a formula that calculates whether DATE 3 is between dates 1 + 2, no doubt due to DATE 2 being just a time, rather than a date.

Generally MEDIAN() would provide the result:

=MEDIAN(Date1,Date2,Date3)=Date3

But you have to change either Date1/Date3 into just a time value or Date2 into a date+time value.