Converting Date/Time Values in the format of "ddd hh:mm

[jd yort]

Hi everyone,

I am having some difficulty converting text when the date/number format is being provided in an unsual format.

An example is "063 09:06". This is referring to a task being open for 63 days, 9hrs and 6 minutes. I need to convert it into values so that i can add the fields together. ie If a second field was "001 08:24". When I added the two together, it should give me a total of "064 17:30".

Does anyone have any ideas to convert these text values?

 

[tactps]

I would use text to columns (data menu) to split the days and times into 2 columns.

Assuming that your days are in cells A1:A2 and the times are in cells B1:B2:
=SUM(A1:A2)+INT(SUM(B1:B2))
will give the number of days

and
=MOD(SUM(B1:B2),1)
will give the number of hours and minutes.

 

[jd yort]

Thank you for the reply, it is close but not exactly right.

I actually have hundreds of these values to sum together. When the hours go over 24, it does not accumulate. ie if you are adding 09:06, 13:24 and 4:54 it should go to 27hours and 24 mins. Using the below formulas it will just display 3:24.

Any other suggestions?

 

[barry houdini]

Where your values are in A1:A10 try this formula

=TEXT(INT(SUMPRODUCT(RIGHT("00000"&A1:A10,5)+LEFT(A1:A10&"000",3))),"000 ")&TEXT(SUMPRODUCT(RIGHT("00000"&A1:A10,5)+0),"hh:mm")

 

[jd yort]

Thank you Barry. That was exactly what I was after.
Your the man.