Time difference based on multiple records

[rajeevs]

Hi

I am looking for a formula or code which can perform a calculation as per the attched file.
The records can be in ACCESS table with an ID.
Please help

[jimatqsi]

rajeevs,
I'm afraid your "question" doesn't make any sense. There's nothing to answer. What do you want? What calculation? Please take the time to spell out clearly what you want. And remember that most people are not going to download your attachment.

Jim

[twinnyfo]

rajeevs,

Exactly what is your table trying to do? Your table (in the expected column) does not seem to make any sense as to what you want it to calculate.

Any time you calculate dates and times can be tricky in Excel or Access. Your spreadsheet "seems" straightforward, but without knowing exactly what you want to do, it is difficult to proerly advise.

[rajeevs]

Thank you jim & twinnyfo
I have a recordset with an entry time field. the records will be in a sort order based on the entry time. I want to find out if any record has an entry time less than 5 minutes compared to the previous record then I need to change the entry time of that record to have a separation of five minutes with the previous record and next record. Hope I have explained the issue correctly. Please help me

[jimatqsi]

Look at the datediff() function. It will return the difference between two dates or times. You can ask for the difference in months, days, years, minutes, seconds, etc ...

[twinnyfo]

rajeevs,

if you have your recordset, sorted by entry time, then simply cycle through those records. You should then compare the times, using the DateDiff() Function.

Private Sub ResetTimeField()
    'This is assuming you already have your recordset
    Dim intRcds As Integer
    Dim intCounter As Integer
    Dim dtPrevious As Date
    With rst
        .MoveLast
        intRcds = .RecordCount
        .MoveFirst
        dtPrevious = !DateEntryField
        .MoveNext
    End With
    For intCounter = 1 To intRcds - 1
        If DateDiff("n", rst!DateEntryField, dtPrevious) < 5 Then
            With rst
                !DateEntryField = dtPrevious + (1 / 24 / 60 * 5)
                dtPrevious = !DateEntryField
            End With
        Else
            dtPrevious = rst!DateEntryField
        End If
        rst.MoveNext
    Next intCounter
End Sub

This code may seem a bit strange, because using the DateDiff() function doesn't work to well when wadding values to dates/times.

In line 16, you have the expression "(1 / 24 / 60 * 5)". This is calculating the decimal expressed by five minutes of time (dates are whole numbers, times are a decimal part of that number).

Hope this helps!

[NeoPa]

Assuming your DateTime values are in Column A and you want the updated values in Column B such that Column B is always at least 5 minutes after Column B of the previous record then place the following formula in Comlumn B (Cell("B2") and drag down :

=IF($B1+5/1440>$A2,$B1+5/1440,$A2)

Make sure the column is formatted for date values if you want to see it that way. It won't be automatic as is usually the case when entering date values.

[rajeevs]

Thank you twinnyfo & NeoPa

First I tried NeoPa's solution in Excel and it worked perfect. Thank you so much.
I wanted to try twinnyfo's code with ACCESS tbl but when I run the code it is showing error 3020.
I define the tbl as SeparationTbl and tried to run the code.
runtime error 3020
"Update or CancelUpdate without Addnew or Edit"
I have the time in ExitTime Field and I want to update another blank field named as NewExitTime with the 5 minutes separation new timing
Hope you will help me to finetune the code and achieve the result. Thank you both of you for the quick solutions

[twinnyfo]

I forgot to include the edit and update commands.

Starting at line 17:

            .Edit
            dtPrevious = !DateEntryField
            .Update

My mistake. Let me know if this corrects your errors.

[rajeevs]

@twinnyfo
Thank you for the time and consideration
I will try agian and let you know

[rajeevs]

Thank you twinnyfo

After running the code it is sequencing the time. That is not the result I am looking for. It need to check 1 record above and one record below and then allocate 5 minutes between the records from their time (same as the attached Image)

[twinnyfo]

Your attachment is unreadable. You are trying to change the current recordset, correct? Please explain.

[rajeevs]

Sorry for the delay. I was on holidy for two days.
I have attached a small excel worksheet to give you an idea about what is my requestsample.xlsx

[NeoPa]

Not sure that works too well either Rajeev, but possible if you download then rename the extension.

I took a snap and attached it to your earlier message though and that now shows more legibly.

PS. As you already have a working answer - why is it that you want a specifically code-based solution? Typically I find that formulas in the cells are preferable, but you seem to want it the other way around. Not a problem, of course, but I'm just curious.

[rajeevs]

I am trying to implement it in an ACCESS DB. Then it will be much easier for future use. Otherwise every time I have to transfer the data to Excel and do it in Excel. Hope I am not asking too much. Thank you NeoPa. twinnyfo's code can do the desired rsult if it is fine tuned which I am unable to do myself. That is why I am bothering you all. Thank you for the understanding

[NeoPa]

Not at all. That's perfectly acceptable :-)

What you will need to decide though, because tables contain sets of data rather than a single ordered list, is which order you are referring to when you specify anything that is next or previous. In set terms those words are meaningless. A little like trying to quote area in meters. It's not possible because area is measured in square meters.

[rajeevs]

@NeoPa
Dear NeoPa
Thank you for the reply. The data will be ordered on the time field and records will be having IDs as well.
So it will be easy to refer to previous record either by ID or the order of time field

[NeoPa]

Are you thinking of updating the data in place or simply showing the amended data when querying the table?

[rajeevs]

@NeoPa
If coding is difficult a query with additionl field with the amended time will also help me

[NeoPa]

What are you actually after. I'm trying to avoid doing it twice. Both are possible.