Dates not consistent

[Nev Wood]

Hi,

I have a spreadsheet which contains user counts on the hour every hour.
However, I know that there are gaps in my data and because I want to compare
this data with data from other sources I have used the following solution:

A B C D
Recorded DateTime Recorded Counts All DateTime All Counts
27/05/2008 07:00 3 27/05/2008 07:00 3
27/05/2008 08:00 4 27/05/2008 08:00 4
27/05/2008 09:00 6 27/05/2008 09:00 #N/A
27/05/2008 11:00 6 27/05/2008 10:00 #N/A
27/05/2008 12:00 10 27/05/2008 11:00 #N/A
etc.

Columns A & B contains all the data that I have, column C contains all dates
and times in this period whether there is a corresponding value in column A
or not, and column D contains a formula which is designed to show the value
from column B if there is data for that date and time or #N/A if not.

The formula I am using in cell D2 is:

=VLOOKUP(C2,A$2:B$1035,2,FALSE)

I would expect to see #N/A in cell D5 as there is no data for 10am, but I
would expect to see values in D4 & D6 as there are values for 9am and 11am.
If I click in the formula bar for cells C4 & C6 and then press enter Excel
gives me the value I expect, and therefore I suspect there is some problem
with Excel not recognizing that the contents of A4 are the same as C4
(although it has worked for rows 2 and 3!!).

Please help, this is driving me crazy!!

 

[Sandy Mann]

I think that you have rounding errors. If I copy and paste the Dates/Times
from you post into Excel I get no #N/A but if I highlight C2:C3 and drag
down in C5 I then get the #N/A in D4, although the Date/Time looks exactly
the same and both A4 & C4 show a value of 39595.375 when formatted as
General. However, =C4-A4 returns 5.7872966863215E-08 indicating that the
incrimenting due to the dragging down has induced a tiny error.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Nev Wood]

Thanks Sandy, looking at the data seems to confirm that this could be the
problem.

Is there any way I can rectify this problem without going to each and every
cell in the spreadsheet?
--
Nev

I think that you have rounding errors. If I copy and paste the Dates/Times
from you post into Excel I get no #N/A but if I highlight C2:C3 and drag
down in C5 I then get the #N/A in D4, although the Date/Time looks exactly
the same and both A4 & C4 show a value of 39595.375 when formatted as
General. However, =C4-A4 returns 5.7872966863215E-08 indicating that the
incrimenting due to the dragging down has induced a tiny error.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Unfortunately we have no way of knowing how many decimal places the time is
out by and global rounding will make some of the correct times then be
wrong. Would the following work for you? It checks if the Date/Time in
Column C is within one minute, or there abouts, of the Date/Time in Column A
and if so it copies the contents of Column A into Column C. It works for me
but I would try in on a COPY of your data just in case.

Sub CorrectIt()
Dim LastRow As Long
Dim Checker As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For Checker = 1 To LastRow
If IsDate(Cells(Checker, 1)) Then
If Abs(Cells(Checker, 1) - Cells(Checker, 3).Value) _
<= 0.00069 Then Cells(Checker, 3).Value = _
Cells(Checker, 1).Value
End If
Next Checker
End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Thanks Sandy, looking at the data seems to confirm that this could be the
problem.

Is there any way I can rectify this problem without going to each and
every
cell in the spreadsheet?