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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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?
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,222,072
Messages
6,163,747
Members
451,855
Latest member
mcook36155

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
Back
Top