I need to add time in the format of 00d 00:00:00

[paraspiral]

I have tried various formulas to be able to add I am pulling from a MySQL database and pasting into an Excel Spreadsheets. The time appears as 00d 00:00:00. Is there any way I can add this into an Excel Cell and come up with a added Sum for say says D2 through D20?

[Pauleyb]

You'll need to convert it to Excel's time format. Assuming you have that text string in A1, this formula will convert it:
=LEFT(A1,2)+MID(A1,5,2)/24+MID(A1,8,2)/(24*60)+MID(A1,11,2)/(24*60*60)

Do that for each string and then you can add up the numbers to get a total. 'Reverse' the formula if you need to get the sum back into the MySQL format.

[joeu2004]

First, verify that D2:D20 contain text, not numeric time. For example, does =ISTEXT(D2) return TRUE?

If so, then ostensibly:

Please Login or Register  to view this content.

If we can assume that d, h, m and s are always 2 digits as you indicated, we can simplify:

Please Login or Register  to view this content.

Of course, the sum of the days might no longer be limited to 2 digits.

[joeu2004]

Please Login or Register  to view this content.

That's silly; left over from an earlier and unnecessarily-complicated thought. It should be:

Please Login or Register  to view this content.

And again, that is necessary only if we cannot assume that d, h, m and s are always 2 digits, as you indicted they are.

[shg]

Another way:

	D	E
2	11d 08:44:11
3	2d 12:35:49
4	5d 18:24:38
5	15d 01:17:03
6	15d 14:13:08
7	6d 11:14:56
8	7d 07:09:21
9	8d 14:56:41
10	4d 15:32:52
11	13d 06:19:52
12	9d 06:42:15
13	3d 19:54:55
14	2d 19:47:26
15	7d 14:08:24
16	12d 23:39:58
17	6d 21:51:47
18	9d 05:26:41
19	14d 16:40:58
20	8d 23:31:12
21	166.925081	D21: {=SUM(--LEFT(D2:D20, FIND("d", D2:D20) - 1), --RIGHT(D2:D20, 8))}
22	166d 22:12:07	D22: =INT(D21) & "d " & TEXT(D21, "hh:mm:ss")