Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Getting a headache

  1. #1
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I have data that includes two Date cols and Two time cols these being date/time of arrival and Date/Time of Departure.

    The data has been imported and occasionally the depart time is showing that the departure time is before the arrival time which cant be.

    I have been trying to write a macro that checks each entry to see if the departure time is less than the arrival and if so change the field colour.

    My problem is this, if an arrival time is say 23:30 hrs and the departure is 01:00 hrs the following morning it gets coloured when it should'nt be.

    Can anyone advise how I may get around this problem.

    King regards

    Kevin

    [ This Message was edited by: swaink on 2002-03-29 02:10 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,572
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default

    I have data that includes two Date cols and Two time cols these being date/time of arrival and Date/Time of Departure.

    Care to tell the exact range that houses your data?


  3. #3
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    The dates are a max of two days ie over one night but the times can be any time during a 24 hour period

    Kevin

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,572
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-29 06:48, swaink wrote:
    Hi there

    The dates are a max of two days ie over one night but the times can be any time during a 24 hour period

    Kevin
    Kevin,

    That's fine. Now the range that holds your data: Is it A2:D100, or what?

    Aladin

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Date1 Date2 Time1 Time2

    20/03/2002 20/03/2002 13:00 16:00 TRUE
    20/03/2002 21/03/2002 23:30 1:00 TRUE

    Formula =AND(D4>=C4,F4-E4+(D4>C4)>0)

    Questions:
    1. Could you edit or reword the line of your original question. "My problem is this, if an arrival time is say 23:30 hrs and the departure is 01:00 hrs the following morning it gets coloured when it should'nt be."

    2. Could you show the macro that you are testing?



    [ This Message was edited by: Dave Patton on 2002-03-29 09:11 ]

  6. #6
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin the date of arr is J2:J1783 and the time is K2:K1783. The Departure date is P2:P1783 with the Time in Q2:Q1783.

    Dave im trying to use the macro below to extract the rows that contains the error and paste them to another worksheet.

    Sub Checktimes()
    Application.ScreenUpdating = False
    Range("A2").Select
    Do Until ActiveCell = ""

    If ActiveCell.Offset(0, 16) > ActiveCell.Offset(0, 10) Then
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Select
    Selection.Copy
    Sheets("Fault Extract").Select
    Range("a1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("System Extract").Select
    ActiveCell.Select
    ActiveCell.Offset(1, 0).Select

    End If
    Loop
    End Sub
    Ps im using col A to work from as it is the only col that contains data in every row.

    [ This Message was edited by: swaink on 2002-03-29 11:06 ]

    [ This Message was edited by: swaink on 2002-03-29 11:08 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You could revise the references as necessary
    and try the formula.

    Filter on False and then copy that information.

    Once you know that the process works, you could automate it.

  8. #8
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    Many thanks I have added the formula and it works great

    Thanks again

    Kev


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •