Need Help Multiplying and Adding digits from the same Cell in Excel

[RakeshSiripurapu]

Dear All,

I am trying to find a solution to simplify a complicated work I deal with at times that takes me hours to perform it manually. I am a starter in MS Excel and I deal with mostly simple MS Excel operations every day at work. I appreciate any solution provided to me on the below Problem I have

ex. 2 19:12:54
I need to Multiply the 2 with 24 and then SUM it with 19 which is after a space in the Cell. So, the result should look like 67:12:54

At times, I come across a situation where I nearly see 200 cells of random numbers in the column where I need to perform this activity manually.

Please help!!

[TMS]

Shouldn't be too difficult. Please post a sample of typical data. Which column is the data in? Which column should the formula go in?

Regards, TMS

[RakeshSiripurapu]

Hi TMS! Thank you for the reply.. as requested, I have attached a sample file..
The only thing I need to accomplish is the SUM of the column after the modifications are done as mentioned earlier.

[JohnTopley]

See attached:

formula is

=IFERROR(LEFT(E2,FIND(" ",E2,1)-1)*24+MID(E2,FIND(":",E2)-2,2)+0&":"&MID(E2,FIND(":",E2)+1,2)&":"&MID(E2,FIND(":",E2)+4,2),TEXT(MOD(E2,1),"hh:mm:ss"))

[RakeshSiripurapu]

Looks Promising TMS! you nailed it!!
Now, I can go home 2 hours early every Friday!! A BIG Thank you! to you..

[ChemistB]

Format the cells as D:hh:mm:ss
and use this formula copied down
=IF(ISNUMBER(E2),E2,RIGHT(E2,8)+LEFT(E2,2))
See attached

[TMS]

Looks Promising TMS! you nailed it!!

Much as I would like to take the credit, the formulae have been provided by John Topley and ChemistB, not I

Regards, TMS


However, if you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.