converting days and hours, mins, and sec

[kelbarben]

I have a report that generates into excel everyday from our avaya system. But if it is more than 24 hours in a time period, it will show up like this, for example: 2d 18:11:49 For my report and formulas, I need it to be in hours, minutes, seconds, so I have to manually change it to 66:11:49. Is there a formula to write that if it sees something that has the #d, it would know to convert that to hours and add it into what follows it? I hope this makes sense. Thanks.

Kelbarben

 

[DonkeyOte]

Maybe:

B1: =IF(ISTEXT(A1)=FALSE,A1,LEFT(A1,FIND("d",A1)-1)+SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1&" ")),""))

Where A1 holds your output string and B1 will hold conversion value or original value if numeric (ie no d in string as I'm presuming this is showing as a numerical value as opposed to text)
B1 formatted to be [hh]:mm:ss

If you want excel to change A1 value automatically I think you'd need to run some code for that...

 

[kelbarben]

That is wonderful! I would have never figured that out!! Can you also help me add in that formula that if it does not have the d, for example 15:50:15, it converts that into the same type number as your formula did for the ones that had the days in it!! You have just fixed months of struggling for me! Thanks!!

Kelbarben

 

[DonkeyOte]

Given the previous solution didn't work for you when there was no "d" every entry must be "text"... I had presumed that where "d" did not exist Excel would interpret the resulting value as numeric, hence:

=IF(ISTEXT(A1)=FALSE,A1,....)

Given this does not work, try:

=IF(ISNUMBER(SEARCH("d",A1))=FALSE,--A1,LEFT(A1,FIND("d",A1)-1)+SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1&" ")),""))

 

[kelbarben]

Perfect! Thank you so much!!

Kelbarben

 

[barry houdini]

Perhaps try

=IF(ISNUMBER(A1+0),A1+0,LEFT(A1,FIND("d",A1)-1)+RIGHT(A1,8))