Question about [h]:mm format from Excel in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a little new to using Access, but I have created about five databases for
my work. However, I'm unable to format an Access text box with the same
format that I use in Excel. To give an example, say I enter the following
data in an Excel spreadsheet:

9:00
9:00
9:00
8:00

If I find the sum of these, Excel will tell me it is 11:00 due to the h:mm
formatting by default. In Excel, if I change the format to [h]:mm it will
output a sum of 35:00.

This is what I want Access to do for me too on a Report. Now, I should
mention that my company uses Access 97. This may have something to do with
the problem I'm having. Access does have the format h:nn, which is the same
as h:mm in Excel. But, you can't add the brackets around the [h] like you
can in Excel.

Does anyone know of a fix for this?
 
The fix is to not store a duration of time (hours or minutes) in a point in
time field. You wouldn't add 9 o'clock to 8 o'clock but you might add 9
hours to 8 hours.

Doug Steele has a generic datediff function at
http://www.accessmvp.com/djsteele/Diff2Dates.html. There are other solutions
that handle situations where the hours is greater than 1 day.
 
Thanks Duane. I appreciate your help.

My database tracks call center specialists logging off the phones for
various reasons. Two of the fields that they enter on my form are "Time
Logged Off" and "Time Logged On." The supervisors want them to enter the
time as a Medium Time for their reporting purposes. However, for my overall
report I need to show how long someone was logged off the phone. In Excel,
I was using the [h]:mm format to show the time off the phones of 3 hrs 10
minutes as 3:10, for example. My Access report is basically subtracting the
Time Logged Off and Time Logged On and then getting the difference of the
two. And, that works fine with the Access format Short Time (hh:nn).
But, I run into problems when I try to get the sum of all the times.





Duane Hookom said:
The fix is to not store a duration of time (hours or minutes) in a point in
time field. You wouldn't add 9 o'clock to 8 o'clock but you might add 9
hours to 8 hours.

Doug Steele has a generic datediff function at
http://www.accessmvp.com/djsteele/Diff2Dates.html. There are other solutions
that handle situations where the hours is greater than 1 day.

--
Duane Hookom
MS Access MVP
--

Josh N. said:
I'm a little new to using Access, but I have created about five databases
for
my work. However, I'm unable to format an Access text box with the same
format that I use in Excel. To give an example, say I enter the
following
data in an Excel spreadsheet:

9:00
9:00
9:00
8:00

If I find the sum of these, Excel will tell me it is 11:00 due to the h:mm
formatting by default. In Excel, if I change the format to [h]:mm it
will
output a sum of 35:00.

This is what I want Access to do for me too on a Report. Now, I should
mention that my company uses Access 97. This may have something to do
with
the problem I'm having. Access does have the format h:nn, which is the
same
as h:mm in Excel. But, you can't add the brackets around the [h] like
you
can in Excel.

Does anyone know of a fix for this?
Click to expand...
Click to expand...
 
I would use:
=Sum(DateDiff("n",[EarlierTime],[LaterTime]))/60
This would result in the number of hours.

--
Duane Hookom
MS Access MVP
--

Josh N. said:
Thanks Duane. I appreciate your help.

My database tracks call center specialists logging off the phones for
various reasons. Two of the fields that they enter on my form are "Time
Logged Off" and "Time Logged On." The supervisors want them to enter the
time as a Medium Time for their reporting purposes. However, for my
overall
report I need to show how long someone was logged off the phone. In
Excel,
I was using the [h]:mm format to show the time off the phones of 3 hrs 10
minutes as 3:10, for example. My Access report is basically subtracting
the
Time Logged Off and Time Logged On and then getting the difference of the
two. And, that works fine with the Access format Short Time (hh:nn).
But, I run into problems when I try to get the sum of all the times.





Duane Hookom said:
The fix is to not store a duration of time (hours or minutes) in a point
in
time field. You wouldn't add 9 o'clock to 8 o'clock but you might add 9
hours to 8 hours.

Doug Steele has a generic datediff function at
http://www.accessmvp.com/djsteele/Diff2Dates.html. There are other
solutions
that handle situations where the hours is greater than 1 day.

--
Duane Hookom
MS Access MVP
--

Josh N. said:
I'm a little new to using Access, but I have created about five
databases
for
my work. However, I'm unable to format an Access text box with the
same
format that I use in Excel. To give an example, say I enter the
following
data in an Excel spreadsheet:

9:00
9:00
9:00
8:00

If I find the sum of these, Excel will tell me it is 11:00 due to the
h:mm
formatting by default. In Excel, if I change the format to [h]:mm it
will
output a sum of 35:00.

This is what I want Access to do for me too on a Report. Now, I
should
mention that my company uses Access 97. This may have something to do
with
the problem I'm having. Access does have the format h:nn, which is the
same
as h:mm in Excel. But, you can't add the brackets around the [h] like
you
can in Excel.

Does anyone know of a fix for this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top