Converting date and hours format
Results 1 to 8 of 8

Thread: Converting date and hours format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Converting date and hours format

    I have a cell that contains a count down to a specific time. The count down appears like this:

    10d 23:18:43

    Is it possible to write a formula that will convert the count down to hh:mm:ss?

    In the above i would expect:

    263:18:43

    Note that if a single day is left the cell looks as such:

    1d 23:18:43

    Thanks in advance!

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,090
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Converting date and hours format

    Quote Originally Posted by kasbac View Post
    I have a cell that contains a count down to a specific time. The count down appears like this:

    10d 23:18:43
    How is this value getting into the cell and what is making it look like that?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date and hours format

    to be honest Rick I am not sure. I was not the creator of the workbook. It is connected to an external source from where it pulls in this value if that helps in any way?

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,090
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Converting date and hours format

    Quote Originally Posted by kasbac View Post
    to be honest Rick I am not sure. I was not the creator of the workbook. It is connected to an external source from where it pulls in this value if that helps in any way?
    So then, the cells contain constant values, not a formulas, correct? If so, select one of those cells... is what is displayed in the cell the same as what is displayed in the Formula Bar? In addition to that question, what are the cells' format... Text?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date and hours format

    Quote Originally Posted by Rick Rothstein View Post
    So then, the cells contain constant values, not a formulas, correct? If so, select one of those cells... is what is displayed in the cell the same as what is displayed in the Formula Bar? In addition to that question, what are the cells' format... Text?
    Yes the value is constant and the value in the cell and the formula bar is the same. The cell format is "General"

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date and hours format

    The following clunky thing should work. Just replace A1 with whatever cell you are referencing. I was not sure whether it would have "0d" if the countdown target is tomorrow, so I included a check for it just in case. Also, if it shows up without days, Excel might treat it as a time value, so I pushed that to text to match the rest of the output in case you were using it in downstream formulas.
    =IF(ISNUMBER(A1), TEXT(A1, "HH:MM:SS"), IF(ISERROR(SEARCH(" ", A1)), A1, (VALUE(MID(A1, SEARCH(" ", A1) + 1, 2)) + VALUE(LEFT(A1, SEARCH(" ", A1) - 2)) * 24) & RIGHT(A1, 6)))

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,090
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Converting date and hours format

    Here is another formula that should work...

    =TEXT(LEFT(A1,SEARCH("d",A1)-1)+TEXT(MID(A1,FIND(" ",A1)+1,8),"h:mm:ss"),"[h]:mm:ss")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date and hours format

    Great thanks gents!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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