DateDiff question

  • Thread starter Thread starter Kelvin Beaton
  • Start date Start date
K

Kelvin Beaton

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
 
I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

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 said:
I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

Kelvin Beaton said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
I think I figured it out...
This seems to work in my query...
=(([outtime]-[intime])*24)-[lunchtime]

Kelvin

Kelvin Beaton said:
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
Click to expand...
Click to expand...
Click to expand...
 
That would work. Then the formula would be
= (DateDiff("n",intime,outtime) /60) - [LunchTime]

Kelvin Beaton said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks!

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

Kelvin Beaton said:
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 said:
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



:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top