How to deduct lunch time 1

[projecttoday]

I have 3 fields, all defined as Date/Time fields:

startdateandtime
enddateandtime
lunchtime

I am trying to calculate the enddateandtime minus the startdateandtime minus the lunchtime.

This much works:

DateDiff("h",startdateandtime,enddateandtime)

But I also need to deduct the lunchtime. How do I do this?


Additional info:
A date and time are stored in startdateandtime and enddateandtime. Only a time is stored in lunchtime. I am using a format of short time for lunchtime and this seems to work for displaying a lunch time. This is a time such as 0:30 (30 minutes), 1:00 (an hour), etc.

 

[dhookom]

How about:

DateDiff("h",startdateandtime+Nz(lunchtime,0),enddateandtime)

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

Format(enddateandtime-startdateandtime-lunchtime,"h:mm")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

FYI,

Date/Time values identify a POINT in time.

Start at this date/time.

End at this data/time.

Your lunch "time" is not a point in time, but rather a DURATION.

Time worked is a duration, calculated by [End Data/Time] - [Start Date/Time] and then subtracting the lunch duration.

[TimeWorked] = [End Data/Time] - [Start Date/Time] - [Lunch Duration]

Of course the DateDiff function can make that simpler (or not)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

dHookum's solution appears to be working. I may try the others, maybe not. Thanks all.

 

[PHV]

dHookum's solution appears to be working
Even for lunch time of 30 minutes ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I would try to be a little more granular than hours

DateDiff("n",startdateandtime+Nz(lunchtime,0),enddateandtime)/60

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And you get .5 for a half hour.

 

[projecttoday]

Actually I'm off today and I didn't test it very well before I left work last night and the database is on the computer at work. I'll test it tomorrow.

 

[projecttoday]

Sure enough, dHookum's first solution does not work for fractions of an hour. So I changed it to his second solution and that appears to be working.

 

[dhookom]

Your initial statement used

DateDiff("h",startdateandtime,enddateandtime)

so I just left it as was thinking you were satisfied. Glad to hear you got this sorted out.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Just to know, did you try mine ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH,
If it makes a difference, I did and it works great ;-)

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, I asked projecttoday !

 

[projecttoday]

Sorry, I did not try it.