+ Reply to Thread
Results 1 to 4 of 4

Need to calculate elapsed time, but excel won't recognize the format as time.

  1. #1
    Registered User
    Join Date
    11-08-2015
    Location
    Maryland
    MS-Off Ver
    Office 2013
    Posts
    2

    Need to calculate elapsed time, but excel won't recognize the format as time.

    I have an excel spreadsheet that has a multitude of dates and times, and I need to calculate the elapsed time between two separate columns in minutes.

    The problem is, the cells are documented in a format that excel doesn't recognize as a time.

    They are in the following format:

    11/8/15 2120 (which correlates to 9:20PM on 11/8/15)

    If I add a colon to the entry so that it would show 11/8/15 21:20, then I can easily calculate the elapsed time with =(B1-A1)*24*60 to get my result in minutes. However, I don't want to spend hours adding a colon to every cell.

    I am not very familiar with Macros. Is there a way I can make a Macros to add a colon as the 3rd to last character in a cell? Or is there a way to show excel that this is a time via formatting the cells?

    Short of manually adding a colon, every iteration I've tried with formatting has led to a "#VALUE!"

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Need to calculate elapsed time, but excel won't recognize the format as time.

    if cell is A1
    then in b1 you could use a formula like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    fill down and then copy and paste values
    it should recognise at time
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-08-2015
    Location
    Maryland
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: Need to calculate elapsed time, but excel won't recognize the format as time.

    That worked!

    So much easier than trying to write a Macro or something crazy.

    Thank you!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need to calculate elapsed time, but excel won't recognize the format as time.

    Or something crazy? Like what
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ 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. Calculate time elapsed, excluding time not spent working
    By BREECHEEZ87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2015, 03:01 PM
  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. Can't seem to calculate elapsed time?
    By homer09001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2013, 08:12 AM
  4. Adding elapsed time to date/time format over 24h
    By KimSenger in forum Excel General
    Replies: 1
    Last Post: 04-02-2013, 06:28 AM
  5. Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  6. Calculate elapsed time by now() ?
    By Desiv5 in forum Excel General
    Replies: 5
    Last Post: 05-07-2010, 05:16 AM
  7. calculate elapsed time
    By Tiresias in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2008, 12:52 AM

Tags for this Thread

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