Change numbers to time format

  • Thread starter Thread starter Pietro
  • Start date Start date
P

Pietro

Hi,

In my query I've a "handlingtime" field contacins numbers that indicate
minutes.
I want to create a new field to have these numbers changed into time format.
Example :
handlingtime NewField
72.2 01:12:12
59 00:59:00
120 2:00:00
 
(...)
In my query I've a "handlingtime" field contacins numbers that
indicate
minutes.
I want to create a new field to have these numbers changed into time
format.
Example :
handlingtime NewField
72.2 01:12:12
59 00:59:00
120 2:00:00
Click to expand...


Use DateAdd function:
DateAdd("n",handlingtime,0)

Examples (in immediate window):
?DateAdd("n",72.2,0)
01:12:00

?DateAdd("n",59,0)
00:59:00

?DateAdd("n",120,0)
02:00:00

K.P.
 
Thank you Krzysztof for your reply.
I did not understand your answer, I need to do the following an a query not
VB.
Thanx again
 
(...)
I did not understand your answer, I need to do the following an a query
not
VB.
Click to expand...

In that event, write something like this:

SELECT handlingtime, DateAdd("n",handlingtime,0) AS NewField
FROM Table1;


K.P.
 
Thank you Krzysztof
It works fine for the minutes and hours, but still it does not work with
seconds.
For example it changes 1.33 minutes to 0:01:00 and 70.66 minutes to 1:10:00
so the seconds are always 00

What do you recommand ?
 
That won't work as written since the dateAdd function use an integer value so
the decimal portion gets truncated - 72.2 will return 01:12:00 and not 01:12:12.

As long as the HandlingTime is under 24 hours (less than 1440), you should be
able to use this expression in your query:
Format(DateAdd("s",HandlingTime * 60, 0),"hh:nn:ss")

Post back for a more complex solution if the handling time is over 1440 minutes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you John veru much...
The below works fine for hours, minutes and seconds.
What about if i'd like to add days?
 
You'd have to write a function to do the formatting for you. That's because,
to Access, a Date/Time value is an eight-byte floating pointing number where
the integer portion represents the date as the number of days relative to
30 Dec, 1899, and the decimal portion represents the time as a fraction of a
day. If you've strictly got a time, to Access it's that time on 30 Dec,
1899. If you have a time that exceeds 24 hours, that means that it's going
to be a time on a different day to Access. For example, 0.5 is Noon (half of
a day), 1.5 would be Noon on 31 Dec, 1899, and 2.5 would be Noon on 1 Jan,
1900.

I talked about this in my October, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html
 
The following will probably work to give you the days, hours, minutes and
seconds in the format d:hh:mm:ss. If you want some other delimiter between
the days and the rest change the ":" to " Days "


HandlingTime\1440 & ":" & Format(DateAdd("s",HandlingTime * 60, 0),"hh:nn:ss")

That is a reverse slash so you will be doing integer division on the value of
HandlingTime.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top