Determining values based on the 'time' given in two cells

[helzbelz1984]

Hi,

This might be completely impossible, but thought I'd ask anyway. Basically, I have three columns like so:

A B C

"Sat May 20 11:39:52 GMT+01:00 2017" "Sat May 20 11:42:41 GMT+01:00 2017"
"Sat May 20 15:04:35 GMT+01:00 2017" "Sat May 20 15:36:57 GMT+01:00 2017"
"Sat May 20 16:44:33 GMT+01:00 2017" "Sat May 20 16:46:07 GMT+01:00 2017"
"Sat May 20 21:47:49 GMT+01:00 2017" "Sat May 20 18:41:17 GMT+01:00 2017"


What I want to determine is whether the time in column B corresponds to within 30 minutes of the time in column A. If it does, I want the corresponding cell in column C to say 'Yes' or 'no' if it doesn't (or words to that effect). So in the first 3 rows given here, C should say 'Yes', and in the 4th row, C should = 'no'.

Is there a way to do this ?

Thanks :-)

[Special-K]

Row 2 should be "NO" since 15:04:35+30 minutes gives 15:34:35
15:36:57 is NOT within 30 minutes of 15:04

[Pete_UK]

Shouldn't the second one be "no" also, as the time difference is more than 30 minutes?

Pete

[Pete_UK]

If you agree to my question, then you can use this in C2:

=IF(ABS(DATEVALUE(MID(B2,9,3)&MID(B2,5,3)&RIGHT(B2,4)) + VALUE(MID(B2,12,8)) - (DATEVALUE(MID(A2,9,3)&MID(A2,5,3)&RIGHT(A2,4)) + VALUE(MID(A2,12,8))))<=30/60/24,"yes","no")

and copy down. Note that I have not taken into account the GMT+01, as this was the same for all your examples.

Hope this helps.

Pete

[Special-K]

Ugh! This is so horribly long but as Ive spent the time on it...

=IF(AND(((MID(B1,9,2)&" "&MID(B1,5,3)&" "&RIGHT(B1,4)&" "&MID(B1,12,8))+0)>=((MID(A1,9,2)&" "&MID(A1,5,3)&" "&RIGHT(A1,4)&" "&MID(A1,12,8))+0),((MID(B1,9,2)&" "&MID(B1,5,3)&" "&RIGHT(B1,4)&" "&MID(B1,12,8))+0)<=((MID(A1,9,2)&" "&MID(A1,5,3)&" "&RIGHT(A1,4)&" "&MID(A1,12,8))+0)+TIMEVALUE("00:30:00")),"Y","N")

UPDATE: Actually, this only allows for 30 minutes AFTER the start time, Pete's solution allows 30 minutes before the start time so his may be the correct answer.

[helzbelz1984]

Wow you people are clever - thank you! (+ yes, row 2 should have been 'no', sorry!)

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete