That worked - it converts the number to hours. BUT it
isn't converting the fractions of numbers to 0:00 format.
(all end with .00)
StartTime and EndTime are formatted as Medium Time.
TRT (the one that calculates the difference as a number)
is formatted as Fixed.
Your expression (that converts it to hours) has no format.
Please help me one last time. You're a genius!!!!
Thank you!!
-----Original Message-----
Are StartTime and EndTime fields that are formatted as
date/time fields? If
yes, then the expression must be changed signficantly. I
had understood that
you were storing the times as fractional hours.
Try this:
TRT: IIf([EndTime]>[StartTime], DateDiff("n",
[StartTime], [EndTime])\60 &
Format(DateDiff("n", [StartTime], [EndTime]) Mod
60, ":00"),
24-(DateDiff("n", [EndTime], [StartTime])\60) & Format (24-
(DateDiff("n",
[EndTime], [StartTime])) Mod 60, ":00"))
--
Ken Snell
<MS ACCESS MVP>
message
OK we're really close, but now it returns 0:00 for all
the
values. What am I missing?
Is there a different path to take?
I have a column of numerical values that represents
hours. This column is a calculation performed on
StartTime and EndTime. (".5" means 1/2 an hour).Could I
say to Access, take whatever value is in that column,
and
for each one individually, convert that value to a
[h]:mm
format?
Please help me just a little bit more. THANK YOU!!!
-----Original Message-----
Sorry - try this:
TRT: IIf([EndTime]>[StartTime], (([EndTime]-
[StartTime])
*24)\60 &
Format((([EndTime]-[StartTime])*24) Mod 60, ":00"),
(24-([StartTime]-[EndTime])*24)\60 & Format((24-
([StartTime]-[EndTime])*24)
Mod 60, ":00"))
--
Ken Snell
<MS ACCESS MVP>
in
message
I get an error saying I need a closing parenthesis.
Since
it looks ok to me, I just added one at the end to
make
it
happy, but then I get an error when I run the
quesry. I
put this expression in a new field section, right?
What else am I missing?
Sorry to keep bugging you.
Thanks
-----Original Message-----
Try this:
TRT: IIf([EndTime]>[StartTime], (([EndTime]-
[StartTime])
*24)\60 &
Format((([EndTime]-[StartTime])*24) Mod 60, ":00",
(24-([StartTime]-[EndTime])*24)\60 & Format((24-
([StartTime]-[EndTime])*24)
Mod 60, ":00"))
--
Ken Snell
<MS ACCESS MVP>
"Christy" <
[email protected]>
wrote
in
message
Thanks for your help.
In your expression, what am I suppossed to put
for "Minutes"? i want it to automatically take
whatever
value in the numerical value column and convert it
into
hours and minutes in the next column. This is the
formula
I am using to calculate the difference in hours:
TRT: IIf([EndTime]>[StartTime],([EndTime]-
[StartTime])
*24,24-([StartTime]-[EndTime])*24)
Is this suppossed to go inside your expression?
Thank you!!!
-----Original Message-----
Calculate the time difference in minutes. Then
use
this
expression for what
you seek:
Result = (Minutes \ 60) & (Format(Minutes Mod
60, ":00")
--
Ken Snell
<MS ACCESS MVP>
in
message
I have a database with differerent TV programs
in
it.
They
all have a start time and an end time. I
created a
query
that calculates the Total running time for
each.
That
value is returned as a fixed number. Is it
possible
to
have that total running time calculated in
hours
and
minuted? Excel lets you do that with the "[h]
mm:ss"
format. Can Access do that?
In other words, how can I convert a number
representing
a
number of hours, into hours:minutes? Say I
want to
convert "17.whatever decimal" into "17:34"
(seventeen
hours and 43 minutes)
Thanks
.
.
.
.