Timesheet

[Billiam]

I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

Hi Howrad,

That was a great help! Thank you for your very excellent detailed
answer...you REALLY HELPED ME! THANK YOU!!!!!
Have a great day,

Billiam

You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard

I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:

You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard

I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[John Spencer]

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:

You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard

I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:

You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[John Spencer]

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

The Access 2007 program takes the spaces out as you have them, I assume this
is okay. here is a copy of what I have in the unbound textbox which is
formatted short time. StartTime and Finsih time are formatted MEDIUM date.

Lunch time is formatted short time. 0:30 = 1/2 an hour (h:mm).

Here is what I have in the control source of the unbound textbox:

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440))/60


The unbound text box displays 12:00

If i use 0:00 for lunch (no lunch) the unbound textbox displays 0:00
If I use 1:00 for lunch (1 hour) the unbound textbox displays 0:00
if I use 1:30 for lunch (1 1/2 hour) the textbox displays 12:00 again

Starttime is medium time
Finishtime is medium time
lunchtime is short time
the unbound textbox is short time

I am at my wits end. The equation calculates correctly without the
Lunchtime...so i assume that is where the problem is...any other
suggestions???
Billiam

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

The problem is the unbound text box which i have a short time format INSTEAD
OF General Number. that produces the correct answer.
Billiam

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[John Spencer]

As long as you are going to be under 24 hours then you can try

Use this to return a time without accounting for lunch
DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0)

Then adjust that by subtracting out the lunch time. So the total expression
might be:

DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.
:

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

GOD BLESS YOU
SIR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thank you thank you thank you for sticking thorugh this with me! You really
are a gentleman and a scholar! have a great weekend....me, i'll be thinking
about time in a whole new way this weekend!

Billiam

As long as you are going to be under 24 hours then you can try

Use this to return a time without accounting for lunch
DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0)

Then adjust that by subtracting out the lunch time. So the total expression
might be:

DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.
:

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

Hi John,

If I was to make StartTime, and finishTime combo boxes that use a
reference/lookuptable for the values, how would I pass the time value
selected to the unbound textbox equation? And if you are still willing to
stick with me, I have a timeInterval reference table for lunch which gives
values every 15 minutes as we are required to report only 1/4 hour amounts,
not specific individual minutes like 9:05 or 7:03, these would be rounded up
or down by the employee...bosses orders!

I have made the combo boxes. and I can get them to display the times from
the reference tables, but the are storing the ID in the form table, and i am
not sure how to also pass them to that expression.

Would you be willing to help again?
Thank you,

Billiam

As long as you are going to be under 24 hours then you can try

Use this to return a time without accounting for lunch
DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0)

Then adjust that by subtracting out the lunch time. So the total expression
might be:

DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.
:

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[John Spencer]

I would store the time from the reference table in your table instead of
storing the reference id. I would probably do this since it makes no sense to
me to store the reference in this case. Your reference table would consist of
only the times that are allowed to be entered and would not need to have a
separate ID column.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

If I was to make StartTime, and finishTime combo boxes that use a
reference/lookuptable for the values, how would I pass the time value
selected to the unbound textbox equation? And if you are still willing to
stick with me, I have a timeInterval reference table for lunch which gives
values every 15 minutes as we are required to report only 1/4 hour amounts,
not specific individual minutes like 9:05 or 7:03, these would be rounded up
or down by the employee...bosses orders!

I have made the combo boxes. and I can get them to display the times from
the reference tables, but the are storing the ID in the form table, and i am
not sure how to also pass them to that expression.

Would you be willing to help again?
Thank you,

Billiam

As long as you are going to be under 24 hours then you can try

Use this to return a time without accounting for lunch
DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0)

Then adjust that by subtracting out the lunch time. So the total expression
might be:

DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

:

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.
:

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[Billiam]

Thank you, John. It makes sense to me as the time is unique to use it as the
pprimary key and bound field. I assume once i set it up that way, that the
time value chosen in the combobox will be picked up in the unbound textbox
expression, or do I need to do something in the afterupdate event to pass
that value to the unbound expression?

Thank you again for your patience and help...I do sincerely appreciate it!
Warmest Regards,

Billiam

I would store the time from the reference table in your table instead of
storing the reference id. I would probably do this since it makes no sense to
me to store the reference in this case. Your reference table would consist of
only the times that are allowed to be entered and would not need to have a
separate ID column.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

If I was to make StartTime, and finishTime combo boxes that use a
reference/lookuptable for the values, how would I pass the time value
selected to the unbound textbox equation? And if you are still willing to
stick with me, I have a timeInterval reference table for lunch which gives
values every 15 minutes as we are required to report only 1/4 hour amounts,
not specific individual minutes like 9:05 or 7:03, these would be rounded up
or down by the employee...bosses orders!

I have made the combo boxes. and I can get them to display the times from
the reference tables, but the are storing the ID in the form table, and i am
not sure how to also pass them to that expression.

Would you be willing to help again?
Thank you,

Billiam

As long as you are going to be under 24 hours then you can try

Use this to return a time without accounting for lunch
DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0)

Then adjust that by subtracting out the lunch time. So the total expression
might be:

DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
It gives the correct answer, but in deciaml format (i.e 8.5 for 8hours 30
minutes which is what I would prefer...anyway to convert this?

:

Break this down into steps to see where the breakdown is.

Field assumptions:
StartTime, FinishTime, and LunchTime are all DateTime fields
The fields LunchTime consists of JUST a time and not a date and a time.

First step:
DateDiff("n",[StartTime],[FinishTime])
returns the number of minutes between the two times.

Second Step:
Int([LunchTime] * 1440)
returns the number of minutes taken for lunch

Third Step:
DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
returns the total number of minutes

Fourth Step:
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60
Note that my parentheses are different from what you posted.
(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Hi John,

afraid this is not working either:

If I use this expression, with the values listed as before, i get this result;

=(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime]*1440)/60)

the result is 12:00

if I enter 0:00 for lunch, which is also a possibility, total hours becomes
0:00

Total Hours is formatted as short time. Also, lunchtime is in the h:mm
format (short time), is this the problem?

Really confused on this, sorry.
:

You need to convert the lunch time into minutes before you do the math.

Try multiplying the lunchtime by 1440 and truncating the result.

DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440)
That should give you the total minutes. If you want the value in hours and
partial hours then divide that by 60
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60

You might want to round the returned value to the nearest tenth or hundredth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I can't seem to get the answer to work out>

I have a field StartTime, data type date/time, Format medium time
Next, I have a field FinishTime, data type date/time, Format medium time
I have a field LunchTime, data type date/time, Format short time

So, start at 7:30 AM
Finish at 4:30 PM
Lunch is 0:30 (for 30 minutes. this is allowed to go over an hour though)

I'm using this expression in an unbound text box called total Hours

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[LunchTime],"Fixed")

I get the answer: 8.98

without the lunchTime part, I do get the correct answer of 9 hours...I think
I must be formatting/converting the lunchtime incorrectly???
Please help!


:


You need to subtract it from the result returned by datediff.

If lunchtime is the time that lunch starts then you need to subtract the
length of the lunchbreak in minutes from the result of datediff ie

=Format(DateDiff("n",[StartTime],[FinishTime])- lunchlength/60,"Fixed")

if your lunchtime field already holds the length they get for lunch (in
minutes) then you can subtract it directly ie

=Format(DateDiff("n",[StartTime],[FinishTime])-[lunchtime]/60,"Fixed")

if the length of the lunchtime is already in hours then subtract it
after you convert their working time from minutes to hours. In this case
you will need an extra set of brackets around the minutes to hours
conversion, the datediff(...)/60 bit ie

=Format((DateDiff("n",[StartTime],[FinishTime])/60)-[lunchtime],"Fixed")

Howard





Billiam wrote:
I am using the following which works. i simply do noot know where to subtract
a person's lunch in the calculation. The field is called 'lunchTime" and is
formatted in short time.

=Format(DateDiff("n",[StartTime],[FinishTime])/60,"Fixed")

Thanks for any help!

Billiam

 

[John Spencer]

Just try it and see if it works. If it fails, post back with how it fails.

And if you wish to STORE the value then bind the comboboxes to the appropriate
fields in your timesheet table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Billiam]

Hi John,

So I was able to get everything working. I also added an afterupdate event
for the form (me.refresh) to get the unbound textbox expression to
show/calculate.

I also have a field called OtherTime, Date/Time, short time format, which I
use as a combobox (uses same ltable like Lunch), to enter in 8 hours (for a
sick day, a vacation day or Stat holiday--our company just pays 8 hours...no
calculation required).

The problem is, that start time finsih time and lunch time do not come into
play now, and so the expression is not working since those values are Null.
It will of course add anything fromthe othertime cbo to the total hours
calculated from starttimefinishtime-lunch, but it will not work when I only
select that time from the othertime cbo)

I expect I need to include an evaluation for Nulls in any of those
fields...could you help me as I cannot seem to get it correct?

The expression I am using in the unbound textbox is:

=([OtherTime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

And then finally, the daily hours are recorded in the timesheet table, per
day, per workcode (Regular hours are paid at a certain rate by the bookeeper
when she gets the exported timesheet report...which I have to design, still,
and IT hours are paid at a different rate, and so they are a possible work
code, too.) in the field TotalDailyHours.

I have been trying to sum these in a query by workcodetype, but I cannot get
the values to display...is it because they are in shorttime format?

 

[Billiam]

Got it...I was trying again after a little sleep, and I was missing a bracket:

=IIf(IsNull([OtherTime]),0,[othertime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

Still stuck on the summing, though :-c

Also, any way to not have the#Error in the unboundtextbox expression until
something gets updated and the afterupdate me.refresh kicks in?

By the way, when this is finished I am willing to post it somewhere for
download if anyone wants it.

 

[Billiam]

Please ignore the post about "got It"...I obviously need more sleep, as it is
not returning a total, just #Error.

Here is what I thought would work:

=IIf(IsNull([OtherTime]),0,[othertime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

Should I be using Nz instead somewhere?

Thank you for sticking with me, I have to try and get this done for Monday
Morning....(-C

Billiam

 

[John Spencer]

Well, the calculation is giving you a POINT IN TIME. Summing points in time
gives you another point in time which includes a date and a time on that date.

If you want to sum DURATIONS of time, you should be calculating the time
worked in some type of units - minutes or hours and adding that to get a duration.

You were better off with the expression that returned 8 or 8.5 hours.

IF (repeat if) you use other time exclusively when it has a value, then this
expression will give you minutes. You can sum this to get a total number of
minutes.
IIF([OtherTime]>0,DateDiff("n",0,[OtherTime]),
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60 )

Then you can divide the results of that expression by 60 to get
hours and fractions of hours.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

So I was able to get everything working. I also added an afterupdate event
for the form (me.refresh) to get the unbound textbox expression to
show/calculate.

I also have a field called OtherTime, Date/Time, short time format, which I
use as a combobox (uses same ltable like Lunch), to enter in 8 hours (for a
sick day, a vacation day or Stat holiday--our company just pays 8 hours...no
calculation required).

The problem is, that start time finsih time and lunch time do not come into
play now, and so the expression is not working since those values are Null.
It will of course add anything fromthe othertime cbo to the total hours
calculated from starttimefinishtime-lunch, but it will not work when I only
select that time from the othertime cbo)

I expect I need to include an evaluation for Nulls in any of those
fields...could you help me as I cannot seem to get it correct?

The expression I am using in the unbound textbox is:

=([OtherTime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

And then finally, the daily hours are recorded in the timesheet table, per
day, per workcode (Regular hours are paid at a certain rate by the bookeeper
when she gets the exported timesheet report...which I have to design, still,
and IT hours are paid at a different rate, and so they are a possible work
code, too.) in the field TotalDailyHours.

I have been trying to sum these in a query by workcodetype, but I cannot get
the values to display...is it because they are in shorttime format?

Just try it and see if it works. If it fails, post back with how it fails.

And if you wish to STORE the value then bind the comboboxes to the appropriate
fields in your timesheet table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Billiam]

John, this expression is giving me a #Name? error...

Well, the calculation is giving you a POINT IN TIME. Summing points in time
gives you another point in time which includes a date and a time on that date.

If you want to sum DURATIONS of time, you should be calculating the time
worked in some type of units - minutes or hours and adding that to get a duration.

You were better off with the expression that returned 8 or 8.5 hours.

IF (repeat if) you use other time exclusively when it has a value, then this
expression will give you minutes. You can sum this to get a total number of
minutes.
IIF([OtherTime]>0,DateDiff("n",0,[OtherTime]),
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60 )

Then you can divide the results of that expression by 60 to get
hours and fractions of hours.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

So I was able to get everything working. I also added an afterupdate event
for the form (me.refresh) to get the unbound textbox expression to
show/calculate.

I also have a field called OtherTime, Date/Time, short time format, which I
use as a combobox (uses same ltable like Lunch), to enter in 8 hours (for a
sick day, a vacation day or Stat holiday--our company just pays 8 hours...no
calculation required).

The problem is, that start time finsih time and lunch time do not come into
play now, and so the expression is not working since those values are Null.
It will of course add anything fromthe othertime cbo to the total hours
calculated from starttimefinishtime-lunch, but it will not work when I only
select that time from the othertime cbo)

I expect I need to include an evaluation for Nulls in any of those
fields...could you help me as I cannot seem to get it correct?

The expression I am using in the unbound textbox is:

=([OtherTime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

And then finally, the daily hours are recorded in the timesheet table, per
day, per workcode (Regular hours are paid at a certain rate by the bookeeper
when she gets the exported timesheet report...which I have to design, still,
and IT hours are paid at a different rate, and so they are a possible work
code, too.) in the field TotalDailyHours.

I have been trying to sum these in a query by workcodetype, but I cannot get
the values to display...is it because they are in shorttime format?

Just try it and see if it works. If it fails, post back with how it fails.

And if you wish to STORE the value then bind the comboboxes to the appropriate
fields in your timesheet table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Thank you, John. It makes sense to me as the time is unique to use it as the
pprimary key and bound field. I assume once i set it up that way, that the
time value chosen in the combobox will be picked up in the unbound textbox
expression, or do I need to do something in the afterupdate event to pass
that value to the unbound expression?

Thank you again for your patience and help...I do sincerely appreciate it!
Warmest Regards,

Billiam