Converting unusual time format

I'm trying to calculate the downtime of some network kit using it's factory tools, but the exported report format for the time period is shown as (for example) "4d 15h 10m" for 4 days, 15 hours and 10 minutes.

Is it possible to convert this format to either hh:mm or dd:hh:mm, so that I can calculate the SUM totals?

Alternatively, if it helps, it also shows the start and end date/time of these periods in the following format:
"Wednesday, September 12, 2012 18:53:17" so it might be possible to work out new downtime periods and use them instead.

Many thanks in advance,
Dave.

With the text:
"4d 15h 10m"
in A1, In B1 enter:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"d ","d"),"h ",":"),"m",":00")
In C1 enter:
=--LEFT(B1,FIND("d",B1)-1)+TIMEVALUE(MID(B1,FIND("d",B1)+1,256)) which displays

4.631944444

In "summable" format

Hi essee

Another version:

Formula:

Please Login or Register  to view this content.

@Jakobshavn

Ah. I fear I may have left out some further information. This works for the dates in that format, but some times are as little as one minute, showing as "1m".

Is the formula easy to amend for that?

Thanks for the quick response, you guys on here are, like, magicians when it comes to Excel!

Kevin, I can't seem to get yours working. Do I need to complete it with any more information?

Thanks again,
Dave.

I broke out the individual units and then summed them. Let me know if you have any questions. Will this work for you?

This small User Defined Function (UDF) should solve your problems. (it will also handle seconds):

Please Login or Register  to view this content.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=UnTiFo(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

Here ae some inputs/outputs:


4d 15h 10m 4.631944444
4d 15h 10m 4.631944444
4d 16h 28m 4.686111111
12m 0.008333333
5m 0.003472222
1h 40m 0.069444444
1h 40m 0.069444444
58s 0.000671296
58s 0.000671296
22h 38m 0.943055556
59s 0.000682870
4d 23h 32m 4.980555556
5m 0.003472222
6d 11h 53m 6.495138889
5m 0.003472222
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
22d 9h 51m 22.410416667
6m 0.004166667
6m 0.004166667
36d 7h 51m 36.327083333
36d 7h 51m 36.327083333
117d 12h 15m 117.510416667
12s 0.000138889
0m 86400s 1.000000000

Glad it's working for you. Days is in Column B.

If the formulas work for you, just ignore the VBA suggestion.

Very Good.........update this post if you require any help.

Thank you for the feedback!

All you need to do is change the format. See the attached workbook. Note that for some formats, some values will display incorrect results. See items hi-lighted.

Have a Great Day!

I know I'm late to the party but here's a formula solution that should work for up to two digit hours, mins or seconds, but up to 3 digit days, in any combination

=SUM(MID("00"&B2&"00000",FIND({"d","h","m","s"},B2&"xxxdhms")-{1,0,0,0},{3,2,2,2})/{1,24,1440,86400})

The only error I see in your formula is for:


0m 86400s

I'm assuming that's not a likely input value - as I said my formula works for up to 2 digit hours, mins and seconds (3 for days)

This formula will return a (text) result like dd:hh:mm:ss, allowing display of days > 31

=IF(COUNTIF(B2,"*d*"),LEFT(B2,FIND("d",B2)-1),0)&TEXT(SUM(MID(0&B2&"0000",FIND({"h","m","s"},B2&"xxxhms")-1,2)/{24,1440,86400}),":hh:mm:ss")