time difference

[Guest]

I have made a table having a field- clockIntime and another field
ClockOutTime --I need to know the difference between the clock in and clock
out time in my query or report.
Please reply ASAP

 

[Rick B]

=(DateDiff("n",[StartTime],[StopTime]))\60 &
Format((DateDiff("n",[StartTime],[StopTime])) Mod 60,"\:00")
This will show it as...
HH:MM



You can do it as just minutes if you'd prefer...
=DateDiff("n",[StartTime],[StopTime])

 

[Guest]

Hi.

Use the DateDiff function to calculate seconds. You can then use that value
to calculate exact minutes, hours, days, etc.

You may also want to have a look at the Diff2Dates function located here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

-Michael

 

[Guest]

i've just seen this post and it was helpful for me so thanks! however, do you
know of any way you can use this time difference calculation to be inputted
into a table field? I want to be able to record the duration but for
accuracy, i would like this to be automatically calculated from the start and
end times. i tried the following:

RatingDuration = DateDiff("n",[StartTime],[StopTime])

but it didn't like me entering the field value before the calculation. i was
hoping it would work something like Excel whereby a cell value can be
calculated based on the inputs of other values but it seems that in Access i
can't then link this calculation up to an existing table field.

Can you help? any advice is greatly appreciated! all the best, emma

=(DateDiff("n",[StartTime],[StopTime]))\60 &
Format((DateDiff("n",[StartTime],[StopTime])) Mod 60,"\:00")
This will show it as...
HH:MM



You can do it as just minutes if you'd prefer...
=DateDiff("n",[StartTime],[StopTime])




--
Rick B

I have made a table having a field- clockIntime and another field
ClockOutTime --I need to know the difference between the clock in and
clock
out time in my query or report.
Please reply ASAP

 

[Rick B]

You don't store calculations in tables. If you already have the start and
stop time in your table, then storing the duration would be redundant. What
happens if someone goes back and changes one of the "start times"? Your
stored duration would then be wrong.

In Access, you store the components of a calculated value (start time, stop
time, interest rate, exchange rate, value, etc.) then you perform your
calculations in your forms, reports, and queries.

You mention Excel. Excel is a spreadsheet. A relational database is NOT
just a fancy spreadsheet. Your tables are buckets where data is stored.
You should not be working in the tables. You can create a form or query (or
report for that matter) that will have a similar look to your spreadsheet
and you can include the calculations there.

Hope that helps.

--
Rick B

i've just seen this post and it was helpful for me so thanks! however, do
you
know of any way you can use this time difference calculation to be
inputted
into a table field? I want to be able to record the duration but for
accuracy, i would like this to be automatically calculated from the start
and
end times. i tried the following:

RatingDuration = DateDiff("n",[StartTime],[StopTime])

but it didn't like me entering the field value before the calculation. i
was
hoping it would work something like Excel whereby a cell value can be
calculated based on the inputs of other values but it seems that in Access
i
can't then link this calculation up to an existing table field.

Can you help? any advice is greatly appreciated! all the best, emma

=(DateDiff("n",[StartTime],[StopTime]))\60 &
Format((DateDiff("n",[StartTime],[StopTime])) Mod 60,"\:00")
This will show it as...
HH:MM



You can do it as just minutes if you'd prefer...
=DateDiff("n",[StartTime],[StopTime])




--
Rick B

I have made a table having a field- clockIntime and another field
ClockOutTime --I need to know the difference between the clock in and
clock
out time in my query or report.
Please reply ASAP

 

[Guest]

Thanks Rick, that was very helpful indeed!!

You don't store calculations in tables. If you already have the start and
stop time in your table, then storing the duration would be redundant. What
happens if someone goes back and changes one of the "start times"? Your
stored duration would then be wrong.

In Access, you store the components of a calculated value (start time, stop
time, interest rate, exchange rate, value, etc.) then you perform your
calculations in your forms, reports, and queries.

You mention Excel. Excel is a spreadsheet. A relational database is NOT
just a fancy spreadsheet. Your tables are buckets where data is stored.
You should not be working in the tables. You can create a form or query (or
report for that matter) that will have a similar look to your spreadsheet
and you can include the calculations there.

Hope that helps.

--
Rick B

i've just seen this post and it was helpful for me so thanks! however, do
you
know of any way you can use this time difference calculation to be
inputted
into a table field? I want to be able to record the duration but for
accuracy, i would like this to be automatically calculated from the start
and
end times. i tried the following:

RatingDuration = DateDiff("n",[StartTime],[StopTime])

but it didn't like me entering the field value before the calculation. i
was
hoping it would work something like Excel whereby a cell value can be
calculated based on the inputs of other values but it seems that in Access
i
can't then link this calculation up to an existing table field.

Can you help? any advice is greatly appreciated! all the best, emma

=(DateDiff("n",[StartTime],[StopTime]))\60 &
Format((DateDiff("n",[StartTime],[StopTime])) Mod 60,"\:00")
This will show it as...
HH:MM



You can do it as just minutes if you'd prefer...
=DateDiff("n",[StartTime],[StopTime])




--
Rick B



I have made a table having a field- clockIntime and another field
ClockOutTime --I need to know the difference between the clock in and
clock
out time in my query or report.
Please reply ASAP

 

[Douglas J. Steele]

You'll need to calculate the difference in seconds, and convert that to
hh:mm:ss.

Easiest to use a function:

Function CSecondsToHMS(DurationInSeconds As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngHours = DurationInSeconds \ 3600
lngMinutes = (DurationInSeconds - (lngHours * 3600)) \ 60
lngSeconds = (DurationInSeconds - (lngHours * 3600)) Mod 60

CSecondsToHMS = Format(lngHours, "0") & ":" & _
Format(lngMinutes, "00") & ":" & _
Format(lngSeconds, "00")

End Function

You'd then use:

=CSecondsToHMS(DateDiff("s",[StartTime],[StopTime]))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Rick...

I have the same situation but I need it to show as HH:MM:SS....can you
help
me do that?

=(DateDiff("n",[StartTime],[StopTime]))\60 &
Format((DateDiff("n",[StartTime],[StopTime])) Mod 60,"\:00")
This will show it as...
HH:MM



You can do it as just minutes if you'd prefer...
=DateDiff("n",[StartTime],[StopTime])




--
Rick B

I have made a table having a field- clockIntime and another field
ClockOutTime --I need to know the difference between the clock in and
clock
out time in my query or report.
Please reply ASAP