Decimal to time

  • Thread starter turks67 via AccessMonster.com
  • Start date
T

turks67 via AccessMonster.com

How do I convert decimal to time for eg

55.80 to 56:20

25.70 to 26:10
 
J

John W. Vinson

How do I convert decimal to time for eg

55.80 to 56:20

25.70 to 26:10
Click to expand...

So you're working in the hexadecimal system, where the number before the
decimal point is hours and the portion after is minutes? Do note that if
you're planning to use an Access Date/Time field, it does NOT support hour
values over 23; a Date/Time field is actually a double float count of days and
fractions of a day since midnight, December 30, 1899, so 26:10 is actually
stored as #12/31/1899 02:10:00am#.

That said - you can construct a *text string* (not a Date/Time) as you
describe with an expression like

Int([yourfield]) + (([yourfield] - Int([yourfield]) * 100) \ 60 & ":" &
([yourfield] - Int([yourfield]) * 100 MOD 60
 
T

TedMi

I'm assuming you want to convert a value representing 55 hrs 80 min to 56
hrs 20 min (or, equivalently, 55 min 80 sec to 56 min 20 sec).
IF (OriginalValue - Int(OriginalValue) >= .60) Then
NewValue=OriginalValue + .4
Else
NewValue=OriginalValue
EndIF

If the Original Value need not be kept, do this instead:
IF (OrignalValue - Int(OriginalValue) >= .60) Then OriginalValue =
OriginalValue + .4

If that is not what you intended, please provide more information.

-TedMi
 
T

TedMi

Sorry, I did not notice that you posted this is Queries NG, and gave VBA
code. If you want to convert the value displayed in a query, put this in the
Field line of the query design grid:
HrsMin: IIF(YourField-Int(YourField)>=.6, YourField+.4, YourField)

As John Vinson says, this is not a Date/time datatype. Instead of converting
to hrs:min, you are much better off converting to fractional hrs. as a
numeric value:
FracHrs: Int(YourField) + (YourField-Int(YourField))/.6

Thus, 55.80 becomes 56.3333333....
-TedMi

TedMi said:
I'm assuming you want to convert a value representing 55 hrs 80 min to 56
hrs 20 min (or, equivalently, 55 min 80 sec to 56 min 20 sec).
IF (OriginalValue - Int(OriginalValue) >= .60) Then
NewValue=OriginalValue + .4
Else
NewValue=OriginalValue
EndIF

If the Original Value need not be kept, do this instead:
IF (OrignalValue - Int(OriginalValue) >= .60) Then OriginalValue =
OriginalValue + .4

If that is not what you intended, please provide more information.

-TedMi
Click to expand...
 
T

TedMi

John: I think you meant sexagesimal (base 60), not hexadecimal (base 16).
-TedMi

John W. Vinson said:
How do I convert decimal to time for eg

55.80 to 56:20

25.70 to 26:10
Click to expand...

So you're working in the hexadecimal system, where the number before the
decimal point is hours and the portion after is minutes? Do note that if
you're planning to use an Access Date/Time field, it does NOT support hour
values over 23; a Date/Time field is actually a double float count of days
and
fractions of a day since midnight, December 30, 1899, so 26:10 is actually
stored as #12/31/1899 02:10:00am#.

That said - you can construct a *text string* (not a Date/Time) as you
describe with an expression like

Int([yourfield]) + (([yourfield] - Int([yourfield]) * 100) \ 60 & ":" &
([yourfield] - Int([yourfield]) * 100 MOD 60
Click to expand...
 
Top