Results 1 to 8 of 8
  1. #1
    Nalaka is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jan 2019
    Posts
    3

    Convert Number to time format

    Hi



    I have a decimal number 0.67.How do i convert this in to time(hh:MM) in a access query.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    set the text box format to: hh:nn:ss
    .67 of a 24 hr day is 1pm will give 16:04:48 HRS

    or alter the table field type to DATE/TIME which will give the same result.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is 0.67 representing a time of day or elapsed time? If that is elapsed time in decimal hour, then 0.67 = 40.2 minutes = 00:40:12 (HH:MM:SS)

    Converting decimal time (or degrees) to an HH:MM:SS string is a common topic. https://support.microsoft.com/en-us/...-decimal-angle
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Nalaka is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jan 2019
    Posts
    3
    Its In double data type. i need to show 0.67 as 1h:07M

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Nalaka View Post
    Its In double data type. i need to show 0.67 as 1h:07M
    How did you get from 1:07 0,67?

    0.67*(1 day) = 16h 4min, i.e. in access when you convert time 16:4 to double, you get 0.67
    0.67*(1 hour) = 0.67*(1/24) = 0,0279166666666667 = 40 min, i.e. when you convert time 0:40 to double, you get 0,0279166666666667, and when you divide this with 0.67 you get 0,041667, which represent 1 hour.

    I don't see any logical way to get 1:07 from 0.67!

  6. #6
    Nalaka is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jan 2019
    Posts
    3
    Thanks
    lets say it is 67minutes.how it can show as 1hrs and 7 minuites

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Time and date are really numeric values [In Excel, In MS Access, In MS SQL Server). Date is integer (the number of days from some agreed date - for MS programs usually 01.01.1900), time is part of day (of 24 hours, so 1 hour is represented by value of 1/24, 1 minute is represented by 1/(24*60), 1.second is represented by 1/(24*60*60)]. Date and time format you apply is for display only - it doesn't change really the value in field.

    So answer to your question is
    Code:
    =67/(24*60)
    and when the formula is in text box, then format text box e.g. as "hh:mm".

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access will accept "hh:mm" and convert it to "Short Time". However, should probably get accustomed to using letter "m" to designate month and "n" for minutes. This will be critical in other functions such as DateDiff.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-12-2014, 09:48 PM
  2. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  3. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  4. Convert Number to Date/Time
    By Jerseynjphillypa in forum Queries
    Replies: 10
    Last Post: 06-13-2012, 12:33 PM
  5. Replies: 1
    Last Post: 08-07-2011, 07:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums