Stop rounding Hours into days

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

Guest

In Excel I can use [ ] as in [h]:mm to give me Hours/Mins and the hours
will not be rounded into days once they get to 24 ? ? ?
Is there a way I can have Hours displayed like this in Access, as I need to
have total Hours in the end report.
 
bungy A/C said:
In Excel I can use [ ] as in [h]:mm to give me Hours/Mins and the hours
will not be rounded into days once they get to 24 ? ? ?
Is there a way I can have Hours displayed like this in Access, as I need to
have total Hours in the end report.
Click to expand...


There is no special format code for this in Access, but you
can use an expression to get what you want. The expression
itself will depend on the value you are trying to format
(total minutes vs a sum of time values). If you have a sum
of time values, try this kind of thing:

=Int(24 * tm) & Format(tm, ":nn")
 
What I have Is a list of flight times in a table from the Airfield where I
work-
IE Launch Land [Land]-[Launch]
09:00 10:00 giving 01:00
09:00 10:30 giving 01:30
when I come to add the total hours(done in a Query), and get to 23:59 it
resets to 00:00 and adds 1 day(24 hours)
I want it to add up but remain as Hours:Mins Ie:- Hours:Mins

48:50
Hope you can help
 
bungy said:
What I have Is a list of flight times in a table from the Airfield
where I work-
IE Launch Land [Land]-[Launch]
09:00 10:00 giving 01:00
09:00 10:30 giving 01:30
when I come to add the total hours(done in a Query), and get to 23:59
it resets to 00:00 and adds 1 day(24 hours)
I want it to add up but remain as Hours:Mins Ie:- Hours:Mins

48:50
Hope you can help
Click to expand...

When you write "10:30" you might mean "ten hours and 30 minutes" or you
might mean the time of "half past ten o'clock". When you use DateTime
fields in a database it ALWAYS means the time of day. If you want to store
durations you should use a long integer and store the number of seconds (or
minutes, etc.), and then convert to the hh:mm:ss format after you have done
all of your math on the numeric values.
 
bungy said:
What I have Is a list of flight times in a table from the Airfield where I
work-
IE Launch Land [Land]-[Launch]
09:00 10:00 giving 01:00
09:00 10:30 giving 01:30
when I come to add the total hours(done in a Query), and get to 23:59 it
resets to 00:00 and adds 1 day(24 hours)
I want it to add up but remain as Hours:Mins Ie:- Hours:Mins

48:50
Click to expand...


Did you try the expression I suggested? If not, why not?
If you did try it, what about the result was incorrect?

Pay close attentention to what Rick said, it is critical
with this kind of calculation. Another consideration is
what you think (Land - Launch) means when the launch time is
before midnight and the land time is after midnight. If you
don't have the date part in both the launch and land times,
you have an ambiguous value.
 
Marsh
First, thank you (and Rick) for your help, I am very new to world of Access
and the various calculations involved, and do appreciate all the help that I
get.

I didn’t try your expression as I was unsure how to implement it. However I
did take notice of what Rick said, and what you said about (Land - Launch)
when the launch time is before midnight and the land time is after midnight,
and ambiguous values.

I have now changed LAND & LAUNCH to be a date and time Value, and have used
DateDiff to obtain the actual time the Aircraft is in the air.
So I basically have a set of records -up to 20 flights per day- with
airborne times for each flight.
Flight times are mostly less than 5 hours duration and usually between 1h
30m and 2h 30m, how would I get the total flight to display in only
Hours:Mins duration.
If it is the expression as you said in your earlier reply then my apologise
(As I said at the beginning I don’t know how to apply it)
Once Again thanks

Bungy
 
PS.
So if I have a number of records which total 1441 (mins), I would like to
display it in a report as Hours:Mins eg (24:01).
 
bungy said:
Marsh
First, thank you (and Rick) for your help, I am very new to world of Access
and the various calculations involved, and do appreciate all the help that I
get.

I didn’t try your expression as I was unsure how to implement it. However I
did take notice of what Rick said, and what you said about (Land - Launch)
when the launch time is before midnight and the land time is after midnight,
and ambiguous values.

I have now changed LAND & LAUNCH to be a date and time Value, and have used
DateDiff to obtain the actual time the Aircraft is in the air.
So I basically have a set of records -up to 20 flights per day- with
airborne times for each flight.
Flight times are mostly less than 5 hours duration and usually between 1h
30m and 2h 30m, how would I get the total flight to display in only
Hours:Mins duration.
If it is the expression as you said in your earlier reply then my apologise
(As I said at the beginning I don’t know how to apply it)
Click to expand...


You didn't mention what units the DateDiff result uses, but
I think it's safe to assume that it's in minutes, i.e you
used DateDiff("n", Launch, Land)

This is different from the situation I was warning you about
earlier, so a different expression is needed. Try this

=TotMins \ 60 & Format(TotMins Mod 60, "\:00")

Be sure to check VBA Help for any part of the expression
that is unfamiliar to you.
 
Back
Top