+ Reply to Thread
Results 1 to 10 of 10

Excel convert Text time formatted 10h 58m to time so I can sum together

  1. #1
    Registered User
    Join Date
    02-04-2016
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Excel convert Text time formatted 10h 58m to time so I can sum together

    Hi,

    My first post here!!

    I have an output from a time logging utility that outputs time as Text format:-

    598h 56m
    524h 58m
    589h 34m
    486h 28m

    I'd like to sum these together but i'm afraid my Excel skills are not too good, I wondered if you could help me sort the information so I can SUM these together.

    Thanks!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Hi, and welcome to the forum

    One way...
    With your data in A1

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    If Times are in A1 to A4 cells

    try

    =SUMPRODUCT(--(LEFT(A1:A4,FIND("h",A1:A4)-1)))+(SUMPRODUCT(--(MID(A1:A4,FIND(" ",A1:A4)+1,2))))/60
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    02-04-2016
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Hi Richard,

    Thanks for the help, although I get this result?

    524h 58m = 524.9666667

    Seems a little out to me, any ideas?

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Quote Originally Posted by slinka View Post
    Hi Richard,

    Thanks for the help, although I get this result?

    524h 58m = 524.9666667

    Seems a little out to me, any ideas?
    Hi,

    Why? 58 minutes is .966667 of an hour hence total hours IS 524.9666667.
    What result do you think you should expect that will allow you to manipulate numbers arithmetically?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    ...just another thought. Did you copy the formula to B1:B4 and then use an
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in say B5

  7. #7
    Registered User
    Join Date
    02-04-2016
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Hi Richard,

    Sorry I forgot to mention that Ideally i'd like to add them together the output as Hours and Minutes

    10H 10m
    11H 11m

    = 21h 11m

    Thanks!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Summing the results based on Richard's formula (individual results in B1 onwards) :

    in B5

    =INT(SUM(B1:B4))&"H "&INT(MOD(SUM(B1:B4),1)*60)&"M"

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Without helper cells, Formatting A6 as [h]:mm
    In A6
    =SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(A1:A4,"h ",":"),"m","")))
    If you want it as text
    =TEXT(SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(A1:A4,"h ",":"),"m",""))),"[h]\h m\m")
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  10. #10
    Registered User
    Join Date
    02-04-2016
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Excel convert Text time formatted 10h 58m to time so I can sum together

    Thank you everyone!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Convert a time formatted cell to be able to calculate as a decimal
    By Thistledown in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2016, 02:51 PM
  2. Convert formatted text to fixed text value (time)
    By steez in forum Excel General
    Replies: 2
    Last Post: 04-10-2015, 03:12 PM
  3. [SOLVED] Fomula: How to convert a TEXT into a usable time which can be formatted
    By arekkusu03 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-17-2013, 08:04 PM
  4. Time stored as text. How can I convert to data and time?
    By matthewbutterworth in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2013, 10:19 AM
  5. convert word text to excel time
    By mat24 in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 04-27-2010, 11:51 AM
  6. convert time imported as text to time format for calculations
    By batfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 07:05 PM
  7. [SOLVED] In Excel how can you convert a cell formatted for time (4:30) to .
    By Lisa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1