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

jd yort

Board Regular
Joined
Aug 7, 2006
Messages
76
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

Board Regular
Joined
Aug 7, 2006
Messages
76
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,455
Latest member
tim851858

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top