Results 1 to 4 of 4
  1. #1
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47

    Converting 8 Digit Time to Standard Time

    I am working on trying to automate our production numbers. Currently, our production numbers that are reported from the plants are based on shifts so I can't just use a date field to compile the data, I have to use the time stamp that is entered. Currently it is entered in an 8 digit format. In excel I am able to convert this to regular time (10:58 AM) by using the following formula =TIME(INT(J2/1000000),MOD(INT(J2/10000),100),MOD(J2/100,100)), where J2 is the 8 digit number. (I found the formula on stack overflow)



    I have tried several things but haven't been able to convert in Access. Technically, it isn't necessary since I can just use an if then statement using the 8 digit numbers to assign shifts, but I would like to have the time stamps available so the plant managers can easily look at the data and see what happened when.

    Thanks for any help.

    Edit* Below is what the data pull for one item looks like in Excel.

    Click image for larger version. 

Name:	DateData.png 
Views:	23 
Size:	55.1 KB 
ID:	36197


    *Update*

    I ended up using the method Ajax suggested with a little change. In order to add the leading zeros I went ahead and reformatted the TrnTime field using TimeStamp: Format([TrnTime],"00000000"). From there I slightly adjusted what Ajax posted and used the following: Time: TimeValue((Left([TimeStamp],2)) & ":" & Mid([TimeStamp],3,2)).

    Thanks for the help!
    Last edited by Rustin788; 11-14-2018 at 02:23 PM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this assumes you have all the values filled w zeros. 2 digit values each.

    Code:
    function Cvt8Time(byval pvTxt) as date
    dim h,n,s
    
    h = left(pvtxt,2)
    n = mid(pvtxt,3,2)
    s =mid (pvtxt,5,2)
    
    Cvt8Time=h & ":" & n & ":" & s
    end function

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure how consistent your data is - how does 224132 translate to 12:22AM? - the 'missing' first two characters should be 00

    have you tried using the TimeValue function?

    TimeValue(iif(len(pvtxt)<8,"00",left(pvtxt,2)) & ":" & mid(pvtxt,3,2))

  4. #4
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Quote Originally Posted by Ajax View Post
    not sure how consistent your data is - how does 224132 translate to 12:22AM? - the 'missing' first two characters should be 00

    have you tried using the TimeValue function?

    TimeValue(iif(len(pvtxt)<8,"00",left(pvtxt,2)) & ":" & mid(pvtxt,3,2))
    I believe it assumes midnight = 00:00:00:00. So 00:22:41:32 would be 22 minutes into the day which is 12:22AM.

    I tried the TimeValue function you listed but it looks like the data is recorded as a number so it gives me a data type mismatch.

    Edit* Actually I didn't have it set as an expression so that did convert the time. It only works properly if there are 8 digits so I will have to play with the data a bit. Thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 05-21-2016, 08:51 AM
  2. Converting DATE & Time
    By johnseito in forum Access
    Replies: 8
    Last Post: 10-05-2013, 10:28 AM
  3. Converting time from text to time
    By virgilwilsonjr in forum Access
    Replies: 4
    Last Post: 01-11-2013, 10:05 AM
  4. Converting UNIX time
    By sharon.chapman7 in forum Programming
    Replies: 3
    Last Post: 09-02-2011, 08:32 AM
  5. Converting a date to length of time
    By Duncan in forum Access
    Replies: 2
    Last Post: 11-10-2010, 05:53 AM

Tags for this Thread

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