Calculations based on time

[Ron]

I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.

 

[Marshall Barton]

I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.

I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

Thank you so much for your answer.
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".

The goal is to subtract 30 minutes for unpaid lunch time from the total time.

Ron

 

[Marshall Barton]

The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.
--
Marsh
MVP [MS Access]

VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".

I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

Marshall, you're the man. It works great. Thanks for giving your tiem to
help us!

Ron

The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.
--
Marsh
MVP [MS Access]

VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".

Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.

I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?

Ron

The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.
--
Marsh
MVP [MS Access]

VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".

Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.

I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Marshall Barton]

Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .
--
Marsh
MVP [MS Access]

I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?

The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.

VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.

Ron

Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .
--
Marsh
MVP [MS Access]

I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?

The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Marshall Barton]

You asked about assigning the hours to a variable, my
response was that "you can not reliably use the values from
calculated controls in VBA code". Note the part about
calculated controls and VBA code. Those are two different
environments that run in different code "threads". Both
environments are reliable, but mixing the two is not. Maybe
all this confision is caused by me applying specific
technical meaning to your vague general terminolgy, I can't
tell how precisely you are using these words.

txtMonHrs is the name name I made up for the text box
**control** (not variable) that calculates the Monday hours.
--
Marsh
MVP [MS Access]

The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.

Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .

I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?


:
The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

Here are two days' boxes:
Sunday time text boxes:
Start Time:
Name: SunStartW1, Control Source: SunStartW1
End Time:
Name: SunEndW1, Control Source: SunEndW1

Calculation text box at bottom of Day:
Name: Text8,
Control Source: =(DateDiff("n",[SunStartW1],[SunEndW1])-30)/60
=============

Monday time text boxes:
Start Time:
Name: MonStartW1, Control Source: MonStartW1
End Time:
Name: MonEndW1, Control Source: MonEndW1

Calculation text box at bottom of Day:
Name: Text6,
Control Source: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60
================
The goal is to get the two total times for the two days, which your DateDiff
calculation does correctly, to add the total of the two days together for a
total of hours worked.
I hope this helps.
Ron

You asked about assigning the hours to a variable, my
response was that "you can not reliably use the values from
calculated controls in VBA code". Note the part about
calculated controls and VBA code. Those are two different
environments that run in different code "threads". Both
environments are reliable, but mixing the two is not. Maybe
all this confision is caused by me applying specific
technical meaning to your vague general terminolgy, I can't
tell how precisely you are using these words.

txtMonHrs is the name name I made up for the text box
**control** (not variable) that calculates the Monday hours.
--
Marsh
MVP [MS Access]

The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.

Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .


Ron wrote:
I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?


:
The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Marshall Barton]

Translating my guesses at the text box control names to the
names you are using, the expression would be:

=Text8 + Text6
--
Marsh
MVP [MS Access]

Here are two days' boxes:
Sunday time text boxes:
Start Time:
Name: SunStartW1, Control Source: SunStartW1
End Time:
Name: SunEndW1, Control Source: SunEndW1

Calculation text box at bottom of Day:
Name: Text8,
Control Source: =(DateDiff("n",[SunStartW1],[SunEndW1])-30)/60
=============

Monday time text boxes:
Start Time:
Name: MonStartW1, Control Source: MonStartW1
End Time:
Name: MonEndW1, Control Source: MonEndW1

Calculation text box at bottom of Day:
Name: Text6,
Control Source: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60
================
The goal is to get the two total times for the two days, which your DateDiff
calculation does correctly, to add the total of the two days together for a
total of hours worked.
I hope this helps.
Ron

You asked about assigning the hours to a variable, my
response was that "you can not reliably use the values from
calculated controls in VBA code". Note the part about
calculated controls and VBA code. Those are two different
environments that run in different code "threads". Both
environments are reliable, but mixing the two is not. Maybe
all this confision is caused by me applying specific
technical meaning to your vague general terminolgy, I can't
tell how precisely you are using these words.

txtMonHrs is the name name I made up for the text box
**control** (not variable) that calculates the Monday hours.

The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.


:
Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .


Ron wrote:
I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?


:
The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Ron]

That makes sense. When I do it, the text box is empty. No error, just
empty. I tried formating it with: nothing (empty), fixed, and general. No
errors, but the field is still empty.
Any other ideas?
Ron

Translating my guesses at the text box control names to the
names you are using, the expression would be:

=Text8 + Text6
--
Marsh
MVP [MS Access]

Here are two days' boxes:
Sunday time text boxes:
Start Time:
Name: SunStartW1, Control Source: SunStartW1
End Time:
Name: SunEndW1, Control Source: SunEndW1

Calculation text box at bottom of Day:
Name: Text8,
Control Source: =(DateDiff("n",[SunStartW1],[SunEndW1])-30)/60
=============

Monday time text boxes:
Start Time:
Name: MonStartW1, Control Source: MonStartW1
End Time:
Name: MonEndW1, Control Source: MonEndW1

Calculation text box at bottom of Day:
Name: Text6,
Control Source: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60
================
The goal is to get the two total times for the two days, which your DateDiff
calculation does correctly, to add the total of the two days together for a
total of hours worked.
I hope this helps.
Ron

You asked about assigning the hours to a variable, my
response was that "you can not reliably use the values from
calculated controls in VBA code". Note the part about
calculated controls and VBA code. Those are two different
environments that run in different code "threads". Both
environments are reliable, but mixing the two is not. Maybe
all this confision is caused by me applying specific
technical meaning to your vague general terminolgy, I can't
tell how precisely you are using these words.

txtMonHrs is the name name I made up for the text box
**control** (not variable) that calculates the Monday hours.

Ron wrote:
The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.


:
Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .


Ron wrote:
I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?


:
The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[Marshall Barton]

As long as Text6 and Text8 are in the form's footer section
and displaying the correct values, it doesn't make sense for
their sum to be blank. Are you sure the total text box is
in the same section as the other calculated text boxes?
Maybe the ForeColor and BaxkColor are the same???

The only other thought I have is that either it is taking a
long time to calculate the total or that Access lost track
of all the calculatioms it's supposed to do. Try clicking
on the total text box and the hitting the F9 key to force a
recalculation.
--
Marsh
MVP [MS Access]

That makes sense. When I do it, the text box is empty. No error, just
empty. I tried formating it with: nothing (empty), fixed, and general. No
errors, but the field is still empty.
Any other ideas?
Ron

Translating my guesses at the text box control names to the
names you are using, the expression would be:

=Text8 + Text6
--
Marsh
MVP [MS Access]

Here are two days' boxes:
Sunday time text boxes:
Start Time:
Name: SunStartW1, Control Source: SunStartW1
End Time:
Name: SunEndW1, Control Source: SunEndW1

Calculation text box at bottom of Day:
Name: Text8,
Control Source: =(DateDiff("n",[SunStartW1],[SunEndW1])-30)/60
=============

Monday time text boxes:
Start Time:
Name: MonStartW1, Control Source: MonStartW1
End Time:
Name: MonEndW1, Control Source: MonEndW1

Calculation text box at bottom of Day:
Name: Text6,
Control Source: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60
================
The goal is to get the two total times for the two days, which your DateDiff
calculation does correctly, to add the total of the two days together for a
total of hours worked.
I hope this helps.
Ron

:

You asked about assigning the hours to a variable, my
response was that "you can not reliably use the values from
calculated controls in VBA code". Note the part about
calculated controls and VBA code. Those are two different
environments that run in different code "threads". Both
environments are reliable, but mixing the two is not. Maybe
all this confision is caused by me applying specific
technical meaning to your vague general terminolgy, I can't
tell how precisely you are using these words.

txtMonHrs is the name name I made up for the text box
**control** (not variable) that calculates the Monday hours.

Ron wrote:
The only place I have the number of hrs for the day is in the calculated text
box that has the DateDiff function in it. But you say I can't rely on a
calculated box.

So I am confused as to what the =txtMonHrs is referring to.


:
Shouldn't be a problem doing that in a text box, but you can
not reliably use the values from calculated controls in VBA
code. Can't you get by with another text box expression
like:
=txtMonHrs + txtTueHrs + txtWedHrs + . . .


Ron wrote:
I have one additional challenge: Now I need to add the total hrs. worked
each day along the bottom, which is in the function you gave me.

I am having trouble assigning that value to a variable so I can add them up
for the 7 days. Suggestions?


:
The calculation is correct, but you are trying to use a time
format on a floating point number not a date/time value. If
you get rid of the text box's Format property, it should
display the correct number of hours.


Ron wrote:
VARIABLES:
MonStartW1 = 8:00 AM
MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30

When I put your suggestion:
=(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned
"0:00".


Ron wrote:
I have a vStartTime and an vEndTime. I want to subtract one from the other,
and then subtract 30 minutes unpaid time for lunch to see how many total
hours a person worked in a day.

I have tried unsuccessfully to use many variations of the =DateDiff function.


:
I think this expression would do that:

(DateDiff("n", vStartTime, vEndTime) - 30) / 60

 

[jim]

I am having the same problem, but I am still having trouble following much of
this discussion since I am a real beginner.

In cell I2 I have a start time.
In cell J2 I have an end time
Cell K2 = J2-I2 (shows hours and minutes)
However, I can't seem to subtract the 30 minutes from lunch.

Jim

 

[Douglas J. Steele]

Sounds as though you're using Excel. This newsgroup is for questions about
Access, the database product that's part of Office Professional. That could
be part of the reason you're having problems using the techniques being
discussed.