DateDiff question

Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have 8.00
hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns what
appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

Calculate the difference in minutes ("n") then divide by 60 to get decimal
hours:
DateDiff("n",[intime],[outtime])/60

Wesley Row


"Kelvin Beaton" wrote:

Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have 8.00
hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns what
appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have
8.00 hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns
what appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

It is really not going to get accurate results for you. One thing you have
to keep in mind that the results you want is a duration, not a time.
Date/time fields store a specific point in time. The results of the
difference between two points in time is not a time, but a quantity that
represents a duration, so it will be a numeric value, not a date/time.

The first question is to what level to you want to report the time, In whole
hours, In Hours with a decimal (one and a half hours would be 1.5), or in
hours and minutes (1 hour, 30 minutes). The you need to determine how it
will be formatted.

In most payroll and timekeeping systems, the requirement is for decimal
hours, so the correct formula would be (excluding lunch):
= DateDiff("n",intime,outtime) /60
Which would return 8.5

You did not say how you calculate or record lunch time, so that is a
separate issue. If you have begin and end times recorded for lunch, use the
same calculation to determine lunch time and subtract it from the total time:

= (DateDiff("n",intime,outtime) - DateDiff("n",Lunchin,Lunchout)) /60



"Kelvin Beaton" wrote:

I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have
8.00 hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns
what appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

thanks for the reply and explination...

The in and out time I'm storing as Date\Time and the lunch time I was
considering number.
I would then create a list of time (number) increments .25, .5, .75, 1, 1.25
etc. for the user to pick from.
Then subtract that from the difference of the two times.

Thanks for your help!

Kelvin


"Klatuu" wrote in message
...
It is really not going to get accurate results for you. One thing you
have
to keep in mind that the results you want is a duration, not a time.
Date/time fields store a specific point in time. The results of the
difference between two points in time is not a time, but a quantity that
represents a duration, so it will be a numeric value, not a date/time.

The first question is to what level to you want to report the time, In
whole
hours, In Hours with a decimal (one and a half hours would be 1.5), or in
hours and minutes (1 hour, 30 minutes). The you need to determine how it
will be formatted.

In most payroll and timekeeping systems, the requirement is for decimal
hours, so the correct formula would be (excluding lunch):
= DateDiff("n",intime,outtime) /60
Which would return 8.5

You did not say how you calculate or record lunch time, so that is a
separate issue. If you have begin and end times recorded for lunch, use
the
same calculation to determine lunch time and subtract it from the total
time:

= (DateDiff("n",intime,outtime) - DateDiff("n",Lunchin,Lunchout)) /60



"Kelvin Beaton" wrote:

I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time
of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if
I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have
8.00 hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns
what appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

That would work. Then the formula would be
= (DateDiff("n",intime,outtime) /60) - [LunchTime]

"Kelvin Beaton" wrote:

thanks for the reply and explination...

The in and out time I'm storing as Date\Time and the lunch time I was
considering number.
I would then create a list of time (number) increments .25, .5, .75, 1, 1.25
etc. for the user to pick from.
Then subtract that from the difference of the two times.

Thanks for your help!

Kelvin


"Klatuu" wrote in message
...
It is really not going to get accurate results for you. One thing you
have
to keep in mind that the results you want is a duration, not a time.
Date/time fields store a specific point in time. The results of the
difference between two points in time is not a time, but a quantity that
represents a duration, so it will be a numeric value, not a date/time.

The first question is to what level to you want to report the time, In
whole
hours, In Hours with a decimal (one and a half hours would be 1.5), or in
hours and minutes (1 hour, 30 minutes). The you need to determine how it
will be formatted.

In most payroll and timekeeping systems, the requirement is for decimal
hours, so the correct formula would be (excluding lunch):
= DateDiff("n",intime,outtime) /60
Which would return 8.5

You did not say how you calculate or record lunch time, so that is a
separate issue. If you have begin and end times recorded for lunch, use
the
same calculation to determine lunch time and subtract it from the total
time:

= (DateDiff("n",intime,outtime) - DateDiff("n",Lunchin,Lunchout)) /60



"Kelvin Beaton" wrote:

I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
Hi there

I want to take two times, StartTime and EndTime and get the difference.
What I want to end up with is a start time of 8:00 am and an end time
of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So if
I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd have
8.00 hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it returns
what appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin

Thanks!

Kelvin
"Klatuu" wrote in message
...
That would work. Then the formula would be
= (DateDiff("n",intime,outtime) /60) - [LunchTime]

"Kelvin Beaton" wrote:

thanks for the reply and explination...

The in and out time I'm storing as Date\Time and the lunch time I was
considering number.
I would then create a list of time (number) increments .25, .5, .75, 1,
1.25
etc. for the user to pick from.
Then subtract that from the difference of the two times.

Thanks for your help!

Kelvin


"Klatuu" wrote in message
...
It is really not going to get accurate results for you. One thing you
have
to keep in mind that the results you want is a duration, not a time.
Date/time fields store a specific point in time. The results of the
difference between two points in time is not a time, but a quantity
that
represents a duration, so it will be a numeric value, not a date/time.

The first question is to what level to you want to report the time, In
whole
hours, In Hours with a decimal (one and a half hours would be 1.5), or
in
hours and minutes (1 hour, 30 minutes). The you need to determine how
it
will be formatted.

In most payroll and timekeeping systems, the requirement is for decimal
hours, so the correct formula would be (excluding lunch):
= DateDiff("n",intime,outtime) /60
Which would return 8.5

You did not say how you calculate or record lunch time, so that is a
separate issue. If you have begin and end times recorded for lunch,
use
the
same calculation to determine lunch time and subtract it from the total
time:

= (DateDiff("n",intime,outtime) - DateDiff("n",Lunchin,Lunchout)) /60



"Kelvin Beaton" wrote:

I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
Hi there

I want to take two times, StartTime and EndTime and get the
difference.
What I want to end up with is a start time of 8:00 am and an end
time
of
4:30 pm and I want to subtract the time taken for lunch.

What I want to end up with is how many hours a person has worked. So
if
I
came in at 8:00 am took an hour lunch and went home at 5:00 pm I'd
have
8.00 hours worked.

I tried this in my query, but if I enter 8:00 am and 4:30 pm it
returns
what appear to be whole numbers. Always rounded up.
DiffADate: DateDiff("h",[intime],[outtime])

Any help would be appreciated

Kelvin