+ Reply to Thread
Results 1 to 6 of 6

Converting time of day to time elapsed

  1. #1
    Registered User
    Join Date
    04-25-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Converting time of day to time elapsed

    Hello!
    I've built a data logger for taking thermal measurements in 10 second intervals and my program records the time of day with each measurement. What I am trying to do is convert my time column into a duration column. While there are plenty of ways to workaround this, like putting intervals of 10 in a column and flash filling the rest, I would like to understand if there is a good way to convert the actual time stamps.

    My original plan was to subtract the first time stamp from all the time stamps which should leave me with a duration but I'm having difficulty subtracting one time from another.

    Please Login or Register  to view this content.
    yields #VALUE! in that cell.
    ^^ Here I have used the actual cell values instead of the references.

    Attached is a sample of my data.

    Thanks!
    Attached Files Attached Files

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

    Re: Converting time of day to time elapsed

    Try this ...

    ="11:05:44" - "11:05:34"

    Format Cells -> Time

  3. #3
    Registered User
    Join Date
    04-25-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Converting time of day to time elapsed

    Thanks for the quick response!
    Just for the record, I have had my cells formatted as time.
    Your suggestion works if i copy paste it into the formula bar but if i try using cell references it produces #VALUE!
    Also, I wound have to manually add quotes to all my time stamps, is there an automated method?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting time of day to time elapsed

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D
    1 Time Temp1 Temp2
    2 11:5:33 24.25 21.5 11:05:43 - 11:05:33
    3 11:5:44 24.25 21.5 11:05:54 - 11:05:44
    4 11:5:54 24 21.25 11:06:04 - 11:05:54
    5 11:6:4 24.25 21.25 11:06:14 - 11:06:04
    6 11:6:14 24.5 21.25 11:06:24 - 11:06:14
    7 11:6:24 25 21.5 11:06:34 - 11:06:24
    8 11:6:34 25.5 21.25 11:06:44 - 11:06:34
    9 11:6:44 26.25 21.25 11:06:54 - 11:06:44
    10 11:6:54 27.25 25.25 11:07:04 - 11:06:54
    11 11:7:4 28.25 22 11:07:14 - 11:07:04
    12 11:7:14 29.25 21.75 11:07:24 - 11:07:14
    13 11:7:24 30.25 21.5 11:07:34 - 11:07:24
    14 11:7:35 31.25 21.5 11:07:45 - 11:07:35
    15 11:7:45 32.25 21.5 11:07:55 - 11:07:45
    16 11:7:55 33 21.5 11:08:05 - 11:07:55
    17 11:8:5 34 21.5 11:08:15 - 11:08:05
    18 11:8:15 35 21.5 11:08:25 - 11:08:15
    19 11:8:25 35.75 21.25 11:08:35 - 11:08:25
    20 11:8:35 36.75 21.5 11:08:45 - 11:08:35
    21 11:8:45 37.5 21.5 11:08:55 - 11:08:45
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    04-25-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Converting time of day to time elapsed

    The goal is to subtract the initial (first) time stamp from every time stamp. So the first time stamp would become 0:00:00, the second would be the difference of the first and the second 0:00:10, third would be the difference of the first and 3rd 0:00:20 and so on.
    so essentially I want it to look like we're counting from zero which would yield a duration.

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

    Re: Converting time of day to time elapsed



    =TRIM(A2)-TRIM($A$2)

    Or ?

    =(ROWS($A$1:A1)-1)*1/8640

+ 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. Converting Elapsed Time to Minutes
    By Danielle327 in forum Excel General
    Replies: 2
    Last Post: 09-16-2014, 02:39 AM
  2. [SOLVED] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  3. Converting and calculating average elapsed time
    By jamminalley in forum Excel General
    Replies: 7
    Last Post: 03-12-2012, 10:31 AM
  4. Using start/end time fields on userform to populate elapsed time on spreadsheet
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2011, 05:07 PM
  5. Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  6. Converting Elapsed Time to Minutes
    By jcoffey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2006, 08:52 AM
  7. Converting Elapsed Time to Minutes
    By jcoffey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2006, 08:38 AM

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