Getting a headache

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Dave,

Many thanks I have added the formula and it works great

Thanks again

Kev
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top