+ Reply to Thread
Results 1 to 6 of 6

Timestamp not displaying properly

  1. #1
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Timestamp not displaying properly

    hello

    I recently got a bar-code scanner to read the VIN on cars being sent thru the carwash. My boss is also wanting to track the frequency of the cars, how often, how many per hour. thus i set the scanner to capture timestamps... i've set it up in this format...

    0004.png

    at the end of the day... i download all the information from the scanner via the dc console UI, which dumps them into an xls. when i open the file this is what i see...

    0001.PNG

    Unfortunately this is not the format that i want. it does not display AM/PM. i want 1:01:01 to show as 1:01:01 PM and 10:01:01 to show as 10:01:01 AM. it only displays AM/PM after i format the cells as time and manually retype the information. (i dont want to retype thousands of scans.

    0002.PNG0003.PNG


    the time stamps for this data set should range from 7:00 AM to 5:00 PM

    thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: Timestamp not displaying properly

    What about 8:00:01 - How can I tell if it is AM or PM?

  3. #3
    Forum Contributor
    Join Date
    03-05-2009
    Location
    usa
    MS-Off Ver
    Excel 2016 32Bit
    Posts
    1,157

    Re: Timestamp not displaying properly

    Format the column as custom to show PM or AM.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655

    Re: Timestamp not displaying properly

    One way:

    Row\Col
    F
    I
    J
    1
    Timestamp - Time
    2
    08:35:31
    8:35:31 AM
    I2: =--F2
    3
    08:59:40
    8:59:40 AM
    I3 and down: =F3 + (F3 - I2 < 0)/2
    4
    09:13:52
    9:13:52 AM
    37
    12:44:34
    12:44:34 PM
    38
    12:54:51
    12:54:51 PM
    39
    01:01:55
    1:01:55 PM
    40
    01:16:28
    1:16:28 PM


    More simply, though, I think you're missing a setting on the scanner to give 24-hour time.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Timestamp not displaying properly

    Quote Originally Posted by shg View Post
    More simply, though, I think you're missing a setting on the scanner to give 24-hour time.
    you're right, i called the software developer and they said to add "tt" to return am or pm, or "HH" to return 24hr format. HH:mm:ss = 13:30:25, hh:mm:ss tt = 1:30:25 PM. They didn't include these extra parameters in the defaults. "here's what your scanner can do, but we're not going to tell you" ...smh

    thanks everyone for their help/input

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655

    Re: Timestamp not displaying properly

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro name not displaying properly
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-28-2014, 01:19 AM
  2. charts not displaying properly
    By davidcpro in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-17-2013, 06:54 AM
  3. Long numbers not displaying properly
    By IM22 in forum Excel General
    Replies: 4
    Last Post: 04-09-2012, 05:10 AM
  4. Formulas not displaying properly
    By jdwoods in forum Excel General
    Replies: 2
    Last Post: 08-31-2011, 12:04 PM
  5. [SOLVED] Shared Workbook not displaying properly
    By Ed in forum Excel General
    Replies: 0
    Last Post: 11-08-2005, 12:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1