How to compare 2 dates and times to get duration

[jacc14]

I am creating a time sheet and want to compare 2 dates and times to find the length of time taken.

1. I have a start date in one field and start time in another. I then want to enter a finish date in another field and finish time in another. I basically want to compare the fields to find the duration in time format. eg 6 hrs and 30 mins = 06:30

Thanks
Christine.

[Jim Doherty]

I am creating a time sheet and want to compare 2 dates and times to find the length of time taken.

1. I have a start date in one field and start time in another. I then want to enter a finish date in another field and finish time in another. I basically want to compare the fields to find the duration in time format. eg 6 hrs and 30 mins = 06:30

Thanks
Christine.

The following function will return the value you require and return nothing if a field is empty. Paste it into a new module and save then you can simply call the function in the query grid with a convention such as this Simply pass your field names into the function

TheField: HoursMins([DateFrom],[TimeFrom],[DateTo],[TimeTo])

Function HoursMins(df, tf, dt, tt)
On Error Resume Next
    If IsNull(df) Or IsNull(tf) Or IsNull(dt) Or IsNull(tt) Then
        HoursMins = ""
    Else
        prd = Nz(DateDiff("n", df & " " & tf, dt & " " & tt))
        If prd = 0 Then
           HoursMins = "00:00"
        Else
           HoursMins = Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")
        End If
    End If
End Function

[NeoPa]

If none of the controls is blank then add [StartDate] to [StartTime, add [EndDate] to [EndTime], then subtract the first result from the second. This can be formatted as "HH:nn" if you choose.

[jacc14]

thank you . This has worked perfect.

However it has give me the idea that i should estimate prior how long i expect the job to take.

I have a magazine that prints 2000 copies an hour. Therefore if i have a start date of 28/05/10 and start time of 18:30 and wish to produce 3000 copies I want to return 28/05/10 in the finish date and 20:00 in the finish time.

Many thanks
Christine

[OldBirdman]

I think that you should first combine the date & time fields into single fields. A date field stores both a date and a time. Having 2 fields, one for date and one for time will cause problems when the elapsed time crosses a date boundry (midnight).
NeoPa's suggestion in Post #3 avoids this by building the single field when needed. You could do this again here, but this issue may come up again as the project gains complexity.
Once you have the start date-time as a single value, then the DateAdd() function will compute the end date-time.

[NeoPa]

I certainly support OB in that. It is a better idea where at all possible.

If the run is always 3,000 copies then you could use a specific value of 1.5 hours. Otherwise, if you have a [PrintRun] value, then the value to add would be :

[PrintRun] / 48000

48,000 relates to 2,000 copies per hour times 24 hours per day. A DateTime value reflects days as whole numbers and partials thereof as a fraction.

[jacc14]

The following function will return the value you require and return nothing if a field is empty. Paste it into a new module and save then you can simply call the function in the query grid with a convention such as this Simply pass your field names into the function

TheField: HoursMins([DateFrom],[TimeFrom],[DateTo],[TimeTo])

Function HoursMins(df, tf, dt, tt)
On Error Resume Next
    If IsNull(df) Or IsNull(tf) Or IsNull(dt) Or IsNull(tt) Then
        HoursMins = ""
    Else
        prd = Nz(DateDiff("n", df & " " & tf, dt & " " & tt))
        If prd = 0 Then
           HoursMins = "00:00"
        Else
           HoursMins = Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")
        End If
    End If
End Function

Hi Jim. I wondered if you could assist. This function has worked a treat but i have just found that when it exceeds 24 hours it will return 00:00. Could you tell me what i need to do.
Many thanks
Chris

[Jim Doherty]

Hi Jim. I wondered if you could assist. This function has worked a treat but i have just found that when it exceeds 24 hours it will return 00:00. Could you tell me what i need to do.
Many thanks
Chris

I have not had a problem with the function personally it returning hours and minutes calculated over 24 hours, beyond midnight boundaries and so on. I have attached a small db for you to compare implementation. Look at the query and table formats inside it to see how it compares with yours.

[jacc14]

Hi Jim
I now realise what i have done . I needed to sum all the times but unless i put in the function AS Date it treated as text. This worked fine for under 24 hrs. However I now realise that i cant put in AS DATE but it does give me a new problem. Is there something i can do to get round this please so i can sum all the times even if over 24 hours.
Thanks
Christine.

[NeoPa]

Christine, you seem to have asked pretty well the same question as you did in post #7, but haven't responded in any way to Jim's post #8. It can be hard to know how to respond in such circumstances, other than repeating the same answer as before, which is presumably not much help.

[jacc14]

Christine, you seem to have asked pretty well the same question as you did in post #7, but haven't responded in any way to Jim's post #8. It can be hard to know how to respond in such circumstances, other than repeating the same answer as before, which is presumably not much help.

Hi. Sorry I should clarify. When I reviewed Jim's zipped file in post #8 I realised what I had done which was to have added "As date" in the module. By doing this I actually converted Jims solution to text which does work still. However I now need to add lots of these times together and because its text it wont let me.
I hope this clarifys the matter.
Thanks
Christine

[Jim Doherty]

Hi. Sorry I should clarify. When I reviewed Jim's zipped file in post #8 I realised what I had done which was to have added "As date" in the module. By doing this I actually converted Jims solution to text which does work still. However I now need to add lots of these times together and because its text it wont let me.
I hope this clarifys the matter.
Thanks
Christine

Christine, the solution to this is in the elements of that function already.

If as I would expect you are embedding the function in your query to give you a textual representation of hours and minutes.

If all you are needing in addition is a summation of the time involved just look at the datediff element in isolation. You will understand that the datediff function returns the calculated minutes for the specific period outlined by the parameters passed in.

So, if you had a column of those minutes (as was shown in my most recent upload) you could sum that column of minutes and calculate the overall hours and minutes using the last element of the function namely :

Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")

The mechanics of this is fairly straightforward to do I just dont know your layout there.

I have attached a revised file that includes a TotalTime function look at the form for the extra bits at the bottom

[NeoPa]

Jim,

I don't know if you noticed, but you seem to have submitted your post before finishing the line of code.

I edited it to put the tags in, but the code is the same as you posted.

[Jim Doherty]

Jim,

I don't know if you noticed, but you seem to have submitted your post before finishing the line of code.

I edited it to put the tags in, but the code is the same as you posted.

Thanks Adrian......am in too much of a rush lately obviously the paste and post didnt do it, have revised it now :)

[NeoPa]

Excellent. I was sure it must have been something as simple as that :)