DateDiff question

[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

 

[Guest]

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

Wesley Row

 

[Kelvin Beaton]

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

Kelvin

 

[Guest]

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

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

 

[Kelvin Beaton]

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

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

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

 

[Guest]

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

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

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

 

[Kelvin Beaton]

Thanks!

Kelvin

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

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

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