Getting a headache

swaink · Mar 29th, 2002, 03:09 AM

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 ]

Aladin Akyurek · Mar 29th, 2002, 03:23 AM

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?

swaink · Mar 29th, 2002, 07:48 AM

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

Aladin Akyurek · Mar 29th, 2002, 08:27 AM

Quote:

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

Dave Patton · Mar 29th, 2002, 09:06 AM

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 ]

swaink · Mar 29th, 2002, 12:05 PM

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 ]

Dave Patton · Mar 29th, 2002, 12:21 PM

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.

swaink · Mar 31st, 2002, 08:53 AM

Dave,

Many thanks I have added the formula and it works great

Thanks again

Kev

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 03:09 AM	#1
swaink Board Regular Join Date: Feb 2002 Location: South UK Posts: 344	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 ]

Mar 29th, 2002, 03:23 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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?

Mar 29th, 2002, 07:48 AM	#3
swaink Board Regular Join Date: Feb 2002 Location: South UK Posts: 344	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

Mar 29th, 2002, 09:06 AM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 ]

Mar 29th, 2002, 12:05 PM	#6
swaink Board Regular Join Date: Feb 2002 Location: South UK Posts: 344	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 ]

Mar 29th, 2002, 12:21 PM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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.

Mar 31st, 2002, 08:53 AM	#8
swaink Board Regular Join Date: Feb 2002 Location: South UK Posts: 344	Dave, Many thanks I have added the formula and it works great Thanks again Kev