+ Reply to Thread
Results 1 to 2 of 2

fixing time format

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    365
    Posts
    63

    fixing time format

    I am trying to clean up data and reference between two different forms. I have data in a .csv file that I cannot change how it exports because it comes from a sensor that I dont have control over. What comes up in the cells time stamp is

    2020-07-27T15.19.30.469.

    this shows date and time put together in one cell. I know how to separate the date with the time by using the LEFT and MID function but when I pull what I want for the time it shows 15.19.30 and I cannot get that cell into the proper format to read as a time value and change it to be hh:mm:ss.0....... so it needs to be 15:19:30.4 so I can link it to my other data source which shows the correct format


    in my sample file it shows the data that come from the sensor in Columns A to D

    Column F is where I have the formula to pull out the date from Column B and I need Column F to show as the proper format hh:mm:ss.0 and changing it as a custom format doesnt work
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: fixing time format

    Try this:

    =--REPLACE( REPLACE(RIGHT(B2,12),3,1,":"),6,1,":")

    Format Cells as h:mm:s.0

+ 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] Fixing the Time Sheet
    By Wolfin2010 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-17-2019, 05:18 PM
  2. fixing a duration in mm:ss entered as a time of day (hh:mm:ss)
    By theobrandt in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-02-2018, 02:20 PM
  3. [SOLVED] Fixing Into A Preferred Format
    By thebrucekt in forum Excel General
    Replies: 8
    Last Post: 11-07-2017, 08:14 PM
  4. [SOLVED] Fixing and Calculating Time
    By MercyMercyMe in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-06-2017, 02:20 AM
  5. Fixing Format for Telephone Numbers
    By chcalissie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-07-2015, 09:42 AM
  6. Locking or Fixing the Format of columns
    By Desree86 in forum Excel General
    Replies: 4
    Last Post: 02-07-2014, 08:43 AM
  7. fixing corrupted files in xls format
    By Mike in forum Excel General
    Replies: 1
    Last Post: 04-30-2006, 02:15 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