Converting date and hours format

kasbac

Active Member
Joined
Jan 2, 2008
Messages
344
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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,057
Office Version
2010
Platform
Windows
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?
 

kasbac

Active Member
Joined
Jan 2, 2008
Messages
344
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,057
Office Version
2010
Platform
Windows
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?
 

kasbac

Active Member
Joined
Jan 2, 2008
Messages
344
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"
 

bjessee

New Member
Joined
Aug 20, 2019
Messages
2
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)))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,057
Office Version
2010
Platform
Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,844
Messages
5,489,221
Members
407,681
Latest member
HoneyBadger914

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top