PDA

View Full Version : [SOLVED] Odd error when comparing times with "dateadd"



Summer
01-28-2016, 05:03 AM
Hi everybody!

i'm fairly new to vba programming, so please bear with me : )


I am trying to write a macro that checks whether there is a timestamp missing in a column that looks like that:

00:00
00:10
00:20
00:30
...
23:50

This succession could be complete or there could be random times missing. All the values will be in these 10min intervals though.



My basic Idea is this:




Dim row as double
dim time as date
dim test as date


Row = 1
Time = 0
Test = 0
x = 0


Do Until Row > 144




Time = Cells(Row, 1)
Test = DateAdd("n", x * 10, 0)




If Time = Test Then

Row = Row + 1

Else

Cells(Row, 3) = "ERROR"
Row = Row + 1
x = x + 1

End If

x = x + 1

Loop



So whenever the code notices that the times are not the same, I want the message "ERROR" to appear and the value for x needs to be adjusted (incremented by 2 instead of 1) so the following timestamps can be tested.


This does work until x =10, so the minutes added = 100, and the time = 01:40.
.

At this point the script finds that, for whatever reason, the values are not identical.
I had the the script output the values of "dateadd" in a different column and compared the cells in different formats to make sure all decimal places are the same: they are.

Same thing happens for x=11, time = 01:50


It works with ALL of the other 10 minute intervals, up until 23:50!
(In this case, I only incremented x by 1 each time, otherwise this couldnt be tested)


I also tried erased the 01:40 cell and input the value manually to make sure nothing else is in there, didn't work.

EDIT: I even copied the 01:40 & 01:50 output from "dateadd" into the original timeline, and run it again, so it would compare the exact same entries. Still doesn't work.


Does anybody have a clue why this doesn't work?

If you need more info, let me know : )

Thanks a lot, guys!

SamT
01-28-2016, 07:08 AM
In your code x starts out as 1/1/1904 00:00:00 So I am not sure why it ever matches. If the values in Column A are Timestamps it may be tht their actual values are off by some micro seconds whereas test is exactly on the second.


Sub VBAX_SamT_IncrementBy10Seconds()

Dim rw As Double 'Row and Time are VBA Keywords. Do not use as variable names
Dim test As Date

rw = 2
test = Cells(1, 1) 'Assumes that the top cell always has a date/time value

Do
If Format(Cells(rw, 1), "ss") <> Format(test, "ss") Then Cells(rw, 3) = "ERROR"

test = DateAdd("n", 10, test)
rw = rw + 1
Loop Until rw > Cells(Rows.Count, 1).End(xlUp).row 'Last used row
End Sub

p45cal
01-28-2016, 07:12 AM
Probably due floating point precision in Excel.
Also your code will put ERROR in rows where there are no errors but more than one interval was missed above.
Try the following which checks for 10 minute intervals between rows but doesn't check a start time of midnight. It also attempts to tell you how many values are missing.
Sub blah()
interval = Round(TimeValue("00:10:00"), 5)
For Each cll In Range("A1:A144").Cells
If interval <> Round(cll.Offset(1) - cll.Value, 5) Then
cll.Offset(1, 2).Value = Round((cll.Offset(1) - cll.Value) / interval, 0) - 1 & " missing value(s)?"
End If
Next cll
End Sub
You can do this with formulae too; either a simpler formula in cell C2:
=ROUND(A2-A1,5)=ROUND(TIME(0,10,0),5)
copied down to give a TRUE/FALSE indication, or a more complex fomula in C2:
=IF(ROUND(A2-A1,5)=ROUND(TIME(0,10,0),5),"",ROUND(ROUND(A2-A1,5)/TIME(0,10,0),0)-1 & " missing values(s)?")
copied down, to give the same indication as the macro above.

Summer
01-28-2016, 08:24 AM
Sam T,
Thanks a lot for your help!

They aren't actual timestamps. The Data comes from a .csv file and is imported into excel, so its easy to see what information they originally contain...so I don't think the decimal places can play a role?!

Your script looks very beginner friendly, but i still can't get it to work. ; )

I'm not sure how "format" works, exactly, but "ss" doesn't work for minutes, does it?
It seems to just check for seconds, and therefore doesn't find any missing times, since all of them have 0 seconds.

Then again, I put "mm" instead, that only outputs "12" everytime and also doesn't find any missing ones.


Thanks again, I appreciate the help!

Summer
01-28-2016, 08:46 AM
Hi p45cal

Thank you very much, works like a charm!


Unfortunately I don't have a clue how...would you mind explaining (very shortly) what it does?


This will be a part of a bigger script so I need to change a few things and don't even know where to start.


e.g. Can I just swap everything after "then" for something else I want to happen and the comparison will still work or are they somehow interdependent?


Thanks : )

p45cal
01-28-2016, 09:08 AM
Sub blah()
'Do a one-time calculation of what 10 minutes is in decimal days and round it to 5 decimal places (it saves recalculating it 2*144 times; because it gets used 2x in the loop):
interval = Round(TimeValue("00:10:00"), 5)
'take each cell in the range A1:A14, one at a time (cll is that cell):
For Each cll In Range("A1:A144").Cells
If interval <> Round(cll.Offset(1) - cll.Value, 5) Then 'take the value of the cell below cll (cll.offset(1)) and take away the value in cll itself, and round the result to 5 decimal places. If that's not equal to the rounded 10 minutes (interval) then do something:
cll.Offset(1, 2).Value = Round((cll.Offset(1) - cll.Value) / interval, 0) - 1 & " missing value(s)?" ' put something in the cell 1 cell below and 2 cells to the right of cll (cll.offset(1,2)). That 'something' is:
'the difference between cll and the cell below it ((cll.Offset(1) - cll.Value)) , divided by 10 minutes (interval), then rounded to nearest whole number, finally subtract 1 to get a guess at how many intervals are missing, then tack on a bit of text.
End If
Next cll 'move onto the next cell (cll)
End Sub

You can do what you like between If… and End If; add lines, change whatever, they're not interdependent.

Summer
01-28-2016, 09:14 AM
sweet, that was very helpful.

Thanks a lot!!

SamT
01-28-2016, 02:37 PM
Yeah, I used Format "ss" instead of "mm". My bad :devil2:

In Excel, all times are stored as decimal parts of 24 hours. A day = 1 and 6 hours = .25

Dates are Stored as integral numbers starting from 1904 (default) 1-1-1904 = 1 and 3-Feb-1904 = 34. 1904 was a leap year, so 1 Jan 1905 = 367.

Experiment, Put =NOW() in A1 and =A1 in B1, Format Cells(B1) as Number with 11 decimal places.

Enter or change a number in any other cell to force the sheet to calculate and watch the time in A1 and the decimal in B1.