Converting date and hours format

[kasbac]

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!

 

[Rick Rothstein]

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]

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]

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]

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]

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]

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")

 

[kasbac]

Great thanks gents!