• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

selecting time period

E

Ellasheba

Member
Hello all


I am trying to workout how to select lines of data where the time period covers between two time periods 2300hrs and 0600hrs. I have 4 columns with many lines of data but have possted 2 example lines as follows:- both include part of the period I want to capture!


A=01/01/2012

B=2200

C=01/01/2012

D=2355


A=01/01/2012

B=2200

C=02/01/2012

D=0400


I'm sure it's probably easy for you Excel whizzkids :) but sadly not for me.....
 
Ellasheba


Firstly, Welcome to the Chandoo.org forums.


Can you post a file with notes to explain and show us what your trying to achieve?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Ella ,


Can you work it out from this ?


1. Set up a few helper columns as follows :


In column E , use the following formula to get a composite date-time :


=A10+B10/2400


Select the date format which shows both date and time.


In column F , use the following formula :


=C10+D10/2400


Now both your original sets of data have been combined into a format which is easier for comparison purposes.


In column G , use the following formula :


=IF(C10+$G$2>A10+$G$1,A10+$G$1,IF(F10<C10+$G$2,A10+$G$1-"24:00",A10+$G$1))


where G1 contains 23:00 , and G2 contains 06:00.


What this does is generate the lower date-time limit.


In column H , use the following formula :


=G10+$G$2+"24:00"-$G$1


This generates the upper date-time limit.


Now you can use an IF statement to see whether the date-times in E and F are between or outside the limits in G and H.


Narayan
 
@Narayan

I don't think you can simply divide by 2400. It works for whole hours, but not for minutes. E.g. 2230 / 2400 = 10:18 PM


@Ellasheba

Lengthy, but single formula answer:

=IF(AND(OR(A2+6/24<A2+INT(B2/100)/24+MOD(B2,100)/24/60,C2+INT(D2/100)/24+MOD(D2,100)/24/60<A2),

OR(A2+1<A2+INT(B2/100)/24+MOD(B2,100)/24/60,C2+INT(D2/100)/24+MOD(D2,100)/24/60<A2+23/24)),

"Do not overlap","Overlap")


Follows the idea from here:

http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

but we have to check for 2 overlaps; the morning of first day and the evening of first day. The other thing adding to length is handling the time format.
 
Wow! That is absolutely amazing! Thanks so much I am blown away by your expert knowledge... I'm gonna try to understand how this works so I can learn from you guys...


E
 
Glad we could help. Feel free to ask any questions as to how/why it works. =)
 
I am attempting to break the formula down and understand it bit by bit, so it may take a while for me to come back to you :))


E
 
You must log in or register to reply here.
Back
Top