Problems with time calculations & DateDiff - Please Help!

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

Hey All,

Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM; then
my Me.txtTimeDecimal = -15.00. If someone can explain why my txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] & "'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime *
24) - 24 - DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime *
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.


Thanks in advanceGina Whipp
 
Gina said:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM; then
my Me.txtTimeDecimal = -15.00. If someone can explain why my txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] & "'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime *
24) - 24 - DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime *
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.
Click to expand...


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
 
Marshall,

I am not sure I understand your reply, at least not all of it. I get my
txtStartTime and txtEndTime not having the date part but I want to turn
txtTimeSpent to txtTimeDecimal.

The other part I don't understand is... you mean if I stored the date part
in txtStartTime and txtEndTime DatDiff would work?

Also note, txtTimeSpent always calcualtes correctly, it's when I try to
convert to txtTimeDecimal I have the problem.

Thanks for the time,
Gina


Marshall Barton said:
Gina said:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM;
then
my Me.txtTimeDecimal = -15.00. If someone can explain why my
txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] &
"'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime
*
24) - 24 - DLookup("sLunchBreak", "tblShift",
"[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime
*
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.
Click to expand...


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
Click to expand...
 
okay this works except for one part:

Me.txtTimeDecimal = Format([txtTimeSpent], "hh") & "." &
Format(txtTimeSpent, "nn")

I want Format(txtTimeSpent, "nn") to show .5 for 30 minutes, .25 for 15
minutes... I want fractions not minutes any ideas, I've tried all kinds of
calculations.

Thanks,
Gina

Gina Whipp said:
Marshall,

I am not sure I understand your reply, at least not all of it. I get my
txtStartTime and txtEndTime not having the date part but I want to turn
txtTimeSpent to txtTimeDecimal.

The other part I don't understand is... you mean if I stored the date
part in txtStartTime and txtEndTime DatDiff would work?

Also note, txtTimeSpent always calcualtes correctly, it's when I try to
convert to txtTimeDecimal I have the problem.

Thanks for the time,
Gina


Marshall Barton said:
Gina said:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM;
then
my Me.txtTimeDecimal = -15.00. If someone can explain why my
txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] &
"'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime *
24) - 24 - DLookup("sLunchBreak", "tblShift",
"[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime *
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.
Click to expand...


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
Click to expand...
Click to expand...
 
Nevermind I got it...

Me.txtTimeDecimal = Format([txtTimeSpent], "hh") + Format(txtTimeSpent,
"nn") * 60 / 3600

Thanks anyway!

Gina Whipp said:
okay this works except for one part:

Me.txtTimeDecimal = Format([txtTimeSpent], "hh") & "." &
Format(txtTimeSpent, "nn")

I want Format(txtTimeSpent, "nn") to show .5 for 30 minutes, .25 for 15
minutes... I want fractions not minutes any ideas, I've tried all kinds of
calculations.

Thanks,
Gina

Gina Whipp said:
Marshall,

I am not sure I understand your reply, at least not all of it. I get my
txtStartTime and txtEndTime not having the date part but I want to turn
txtTimeSpent to txtTimeDecimal.

The other part I don't understand is... you mean if I stored the date
part in txtStartTime and txtEndTime DatDiff would work?

Also note, txtTimeSpent always calcualtes correctly, it's when I try to
convert to txtTimeDecimal I have the problem.

Thanks for the time,
Gina


Marshall Barton said:
Gina Whipp wrote:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM;
then
my Me.txtTimeDecimal = -15.00. If someone can explain why my
txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] &
"'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime *
24) - 24 - DLookup("sLunchBreak", "tblShift",
"[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime *
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
Click to expand...
Click to expand...
Click to expand...
 
I thought you were concerned about getting negative values
for TimeSpent. If that's not the issue then I won't worry
about it :-\

Yes, if the date part were were in the fields with the time
values, then DateDiff would not have a problem when your
start and end times cross over midnight.

To keep things in context, I'll respond to one of your other
posts about the decimal calculation part of your question.
--
Marsh
MVP [MS Access]


Gina said:
I am not sure I understand your reply, at least not all of it. I get my
txtStartTime and txtEndTime not having the date part but I want to turn
txtTimeSpent to txtTimeDecimal.

The other part I don't understand is... you mean if I stored the date part
in txtStartTime and txtEndTime DatDiff would work?

Also note, txtTimeSpent always calcualtes correctly, it's when I try to
convert to txtTimeDecimal I have the problem.


"Marshall Barton" wrote
Gina said:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM;
then
my Me.txtTimeDecimal = -15.00. If someone can explain why my
txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] &
"'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime
*
24) - 24 - DLookup("sLunchBreak", "tblShift",
"[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 - Me.txtEndTime
*
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.
Click to expand...


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
Click to expand...
Click to expand...
 
I'm not sure that really works in all situations??

The issue here is that you are treating a duration of time
as if it were a time of day. I.e. 3pm - 9am is NOT 6am
which is what you are doing. If it really does work, then
you could simplify your code to:

Me.txtTimeDecimal = Format(Me.txtTimeSpent, "hh\.nn")

To display your time spent, I think this is more in keeping
with your calculation approach and avoids thinking about
the value as if it were a time of day:

Me.txtTimeDecimal = Format(Me.txtTimeSpent * 24, "0.00")
--
Marsh
MVP [MS Access]


Gina said:
Nevermind I got it...

Me.txtTimeDecimal = Format([txtTimeSpent], "hh") + Format(txtTimeSpent,
"nn") * 60 / 3600


Gina Whipp said:
okay this works except for one part:

Me.txtTimeDecimal = Format([txtTimeSpent], "hh") & "." &
Format(txtTimeSpent, "nn")

I want Format(txtTimeSpent, "nn") to show .5 for 30 minutes, .25 for 15
minutes... I want fractions not minutes any ideas, I've tried all kinds of
calculations.
Click to expand...
Click to expand...
 
Hmmm, yes my calculations work in every circumstance. I was not concerned
about the negative values as much as I was concerned it wasn't even givng me
the correct values but thanks to your reply I understand why.

My txtStartTime and txtEndTime are formatted with no date BUT calculate
txtTimeSpent (Short Time) correctly, it's when I try to turn it to
txtTimeDecimal there is the problem. txtTimeDecimal is is fixed.

Thanks,
Gina

P.S. Just 'cause I got it right doesn't mean I can't learn something for the
next time! 8-)

Marshall Barton said:
I thought you were concerned about getting negative values
for TimeSpent. If that's not the issue then I won't worry
about it :-\

Yes, if the date part were were in the fields with the time
values, then DateDiff would not have a problem when your
start and end times cross over midnight.

To keep things in context, I'll respond to one of your other
posts about the decimal calculation part of your question.
--
Marsh
MVP [MS Access]


Gina said:
I am not sure I understand your reply, at least not all of it. I get my
txtStartTime and txtEndTime not having the date part but I want to turn
txtTimeSpent to txtTimeDecimal.

The other part I don't understand is... you mean if I stored the date
part
in txtStartTime and txtEndTime DatDiff would work?

Also note, txtTimeSpent always calcualtes correctly, it's when I try to
convert to txtTimeDecimal I have the problem.


"Marshall Barton" wrote
Gina Whipp wrote:
Also, I need to convert my time caculation to decimal. The below code
actully worked until txtStartTime = 03:00 PM and txtEndTime = 01:00 AM;
then
my Me.txtTimeDecimal = -15.00. If someone can explain why my
txtTimeDecimal
isn't working when the time crosses the day it would really help.

If Not IsNull(Me.txtStartTime) And DLookup("apEmploymentStatus",
"tblAssociateProfile", "[apAssociateID]=" & "'" & Me![txtAssociateID] &
"'")
= "Active" Then
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime
*
24) - 24 - DLookup("sLunchBreak", "tblShift",
"[sShiftID]=Form.txtShiftID")
* 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime) +
DLookup("sLunchBreak", "tblShift", "[sShiftID]=Form.txtShiftID")
Else
Me.txtTimeDecimal = Abs((Me.txtStartTime - 1) * 24 -
Me.txtEndTime
*
24) - 24
Me.txtTimeSpent = (Me.txtStartTime - 1 - Me.txtEndTime)
End If

I thought about using DateDiff but...

?DateDiff("h","03:30 PM ","01:00 AM") comes up with -14 it's driving me
carzy.


The problem is that your start and end times are missing
their date parts.

You really should use DateDiff, but if you want to know
about parts of an hour, you will need to calculate the
number of minutes in the difference.

Once you have the number of minutes in txtTimeDecimal, you
can display it in txtTimeSpent using an expression:

txtTimeSpent = txtTimeDecimal \ 60 & Format(txtTimeDecimal
Mod 60, "\:00")
Click to expand...
Click to expand...
Click to expand...
 
Hi gina,

what you are adding/subtracting should be a Date and a Time,
not just a time. Since Date is the whole number and Time is
the decimal part of a number, you can add the 2 together.

dim mDateAndTime as Date
mDateAndTime = [DateField] + [TimeField]

It is best to just store Date and Time together.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Date and Time using IIf statements - Please Help Me 1
calculating date/time with blank cells 2
Calculating weighted averges betwwn different time periods 6
Please help with time calculation 7
Help with Time being rounded off on timesheet 4
Problems with Custom Sub to Import to User-Defined Fields 3
IS THIS A HOPELESS CASE FOR SERVICE PACK 2? PLEASE HELP 9
problem with military time and making calculations 12

Back
Top