+ Reply to Thread
Results 1 to 28 of 28

Converting minutes, seconds and milliseconds to a decimal value in seconds

  1. #1
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hello everyone,

    Some things are either terribly difficult in Excel (or it's me ;-)).

    Can't seem to convert a simple "01:04.6" into a decimal value in seconds. The units are in "minutes:seconds", so this should read 01 minute, 4.6 seconds and I wish to convert it to 64.6 seconds. It tried different formulae and format settings but the "01" for example is always wrongly taken as hours. Surely there must be a simple formula to give me the "64.6" as answer. Ideas anyone?

    Best regards,
    Excel_Arate

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Try multiplying your time value by 86400 (note that you will have to change the cell's format to General after entering your formula).

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    To me, some of the question is about data entry -- exactly how do you want to enter the value. Another part is how will Excel read the data entered -- as you've already discovered, when given a 00:00 type of data entry, it prefers to interpret as hours:minutes rather than minutes:seconds. And the third part is how will you use this value -- do you want it stored in Excel's date/time format where the underlying cell value will be a fraction of a day, or do you want the cell to contain the actual seconds.decimalseconds value?

    When I want decimal minutes or decimal seconds and I don't want to worry about the vagaries of Excel's date/time serial number system:

    1) I enter the minutes + seconds as mm.ssss, so I would enter your example as 1.046
    2) Then I can use the DOLLARDE() function to convert to decimal minutes =DOLLARDE(A1,60)
    3) If I want decimal seconds, convert decimal minutes to decimal seconds using an appropriate unit conversion. =60*DOLLARDE(A1,60) or =CONVERT(DOLLARDE(A1,60),"min","sec")
    4) If I ever want to go back to mm.ssss, I can use the DOLLARFR() function =DOLLARFR(function of DOLLARDE(...),60)

    As noted, it really depends on how you want to interact with the sheet, but I find this approach very useful.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Rick

    Thank you for your answer. It doesn't seem to work for me, however. Attached please find a section of the data I am wrestling with. In one case I get the answer 3900 in Cell M29, while I should get 65. Multiplying by 1440 instead of 86400 would have achieved this. This, however, does not work as soon as you have seconds with decimal figure, such as the on right next to it.

    Is there a way to adapt this?

    Many thanks for your help so far.

    Exce-Arate
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    M36 is a time of 1 hr & 5min, which is why you get the "wrong" result, try
    =M36*(IF(ISNUMBER(M36),1440,86400))

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Use the correct cell format.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  8. #8
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Shorty,

    Many thanks for taking this up! Unfortunately, I cannot change the entry, it is already given. So no elbow room there.

    I have also treid the DOLLARDE function but do not get the desired result. Please have a qucik look at the attachment. I have used your formula and added what the result should have been. Did I misunderstand your iunstructions?

    Many thanks for your help so far.

    Cheers,

    Excel_Arate
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Please try at M28:N28

    =M36*1440*60^ISTEXT(M36)

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    The first problem is that some entries are numbers (fraction of a day based on an hh:mm input) and some entries are text. So, if we cannot change data entry, then our solution needs to detect number and text entry and treat them differently. IF(ISTEXT(M36),...)

    If the entry is text, we will assume that it is a text string mm:ss.sss kind of entry. In this case, replace the "." with nothing, then replace ":" with "." to get mm.ssssss. Then use the DOLLARDE() function on the resulting value. DOLLARDE(SUBSTITUTE(SUBSTITUTE(M36,".",""),":","."),60)*60

    If the entry is a number, then we have to detect the entries that are "time" serial numbers (fractions of a day) and those that are already simple decimal seconds. Assuming there will never be date information in these times, perhaps a simple test for less than 1 (since time only serial numbers are between 0 and 1) IF(M36<1,...)

    If the entry is determined to be a time serial number, convert days to decimal hours (which should be equivalent to the desired decimal minutes -- would need more examples to verify), then multiply by 60. CONVERT(M36,"day","hr")*60. If the entry is already a decimal second value, then simply copy the value.

    Nest it all together =IF(ISTEXT(M36),DOLLARDE(...),IF(M36<1,60*CONVERT(...),M36))

    This is not going to work for entries that are less than 1 second and entered as 0.x. This is also not going to work for any entries that include date information. If you are unfamiliar with how Excel stores time (and date) information, I recommend something like this: http://www.cpearson.com/Excel/datetime.htm

    See if that helps any.

  11. #11
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Fluff13,

    Thank you for your suggestion. Pls. see attachment. I have entered the formula in M29 and N29. It works fine in M29 but not in N29 where for some reason the decimal throws the formula off course - it is not recognized as a number. Just to make sur I have copied the formats, but still no luck

    Any ideas where I went wrong?

    Regards,
    Excel_Arate
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    When I open your file I see
    Excel 2016 (Windows) 32 bit
    M
    N
    28
    65
    64.6
    29
    65
    64.6
    Sheet: Freestyle

    Do you also get #VALUE with the formula from Bo_Ry ?
    Last edited by Fluff13; 08-25-2020 at 03:18 PM.

  13. #13
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Bo_Ry
    Interesting lookig formula and it actually works for the M36 but not the N36...perhaps because the time with the decimal isn't recognized as a number (despite copying the formats).
    Regards,
    Excel_Arate
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Fluff13, Yes Bo_Ry's solution led to the same result. Interesting... I suspect this might have something to do with the language version of Excel (min is in German). If it is saved an re-opened in another language, it get's translated automatically. Could you maybe, save the file as you have it and upload it again for me? Then I guess it might work for me as well. Many thanks for your efforts.
    Excel_Arate

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Ok, here you go
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Fluff13,

    Thank you, that part worked fine all of a sudden (yours and Bo_Ry's)! I tried it out on a few other cells and for most of them the solution worked there too. However I got stuck again, when working out the result of S36 (in S28). The problem is the messy entry, which we must deal with - the 55.4 are seconds and could actually be taken as such (the formula gives me 79776). The problem is rcognizing such cases in a formula so I can use an IF statement. I thought about adding a "0:" in front but then I get "#value" as an error message". S29, referring to S37 also gives me "#value".

    Any ideas on this?

    Excel_Arate
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Did you try mine (post 10) where I assumed values <1 would be time serial numbers and values >1 are already decimal seconds values? Of course, a similar test could be added to Fluff13's or Bo_Ry's solution, too. It's just a matter of telling your Excel formula how to distinguish between time serial numbers and numbers that are already entered as decimal seconds.

  18. #18
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi MrShorty - Thank you for your interesting input. I was in fact looking at it, but I guess I must go into more depth as to what I must replace with what, i.e. would it always make sense to swap all the "." for nothing in the first step? What complictaes things a bit here, is that I am working with the German formatting and your "." are my ",". For now I must let matters rest and look at things afresh tomorrow. Many thanks, will get back!
    Excel_Arate

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    would it always make sense to swap all the "." for nothing in the first step? What complictaes things a bit here, is that I am working with the German formatting and your "." are my ",".
    The approach is not dependent on using "." for the decimal separator. If your system uses "," for a decimal separator, then use "," in place of "." This part of the algorithm is just a text manipulation that should not depend on language.

    The only complication I could see is if you will sometimes use "," and other times use "." as decimal separators and need Excel to query your system to determine which decimal separator is being used.

  20. #20
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    How about change "." to ","

    =IFERROR(M36*1,SUBSTITUTE(M36,".",","))*1440*60^ISTEXT(M36)
    Last edited by Bo_Ry; 08-26-2020 at 01:58 AM.

  21. #21
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi Bo_Ry

    I hope I got your formula right, I had to make a small adaption - please see attachment. Result: works for M36 but not N36.

    Regards,
    Excel_Arate
    Attached Files Attached Files

  22. #22
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Replace can't be use like this
    REPLACE(N36;".",LEN(N36);",")

    REPLACE(old_text, start_num, num_chars, new_text)
    Start number , not text



    =IFERROR(N36*1;SUBSTITUTE(N36;".";";"))*1440*60^ISTEXT(N36)
    or

    =IFERROR(N36*1;REPLACE(N36;LEN(N36)-1;1;","))*1440*60^ISTEXT(N36)
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi MrShorty,

    Again, thank you for taking the time with this. I tried following the steps you suggested but didn't get the desired results. Please open the attachment where I have inserted the formula and explained what happens after each step.

    Best regards,
    Excel_Arate
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Wow, this seems to work! One problem, however. WHen I open the file, I see the correct results. When I click onto the cell with the formula and leave it again (e.g. be pressing "return") I get an error message "#value". I have tried it several times and am sur not to have changed anything. What can I do about this?
    Excel_Arate

  25. #25
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    N36 comment: You appear to have performed the two Find-Replace commands outside of the formula, where I was under the impression that you wanted the formula to perform the Find-Replace task (hence the SUBSTITUTE() functions in my proposed solution). I must have misunderstood -- do you want the formula to handle the text replacements inside of the formula, or do you want to do the Find-Replace outside of the formula? If you want the formula to do the text replacements, leave the entry as it was originally entered and use the SUBSTITUTE() functions inside of the formula. If you want to manually perform the Find-Replace operations outside of the formula, drop the SUBSTITUTE() functions from the formula.

    M36 comment: 0.045139 is the serial number for 1:05 AM (1/24+5/60/24=0.045139). Naturally, this value is not at all the input we are expectiing for the DOLLARDE() function as programmed. Starting with this value, 65 minutes (same as seconds for our purposes) is a simple unit conversion from days to minutes. =CONVERT(M36,"day","mn") or =M36*24*60. Remember that this 0.045319 is the actual value of this cell whether it is formatted as time or number or text or scientific or....

    Your formulas in M29:N29 are only the DOLLARDE() formula that I proposed for the "text" conversion scenarios. As I tried to explain earlier, your original data has 3 scenarios that it must consider -- is the data text or number and, if it is a number, is it a time serial number (value between 0 and 1) or a decimal seconds number (I assumed this would be a value greater than 1). The formula I originally proposed would have the DOLLARDE() nested with a pair of IF() functions to make the decisions M29 =IF(ISTEXT(M36),DOLLARDE(SUBSTITUTE(SUBSTITUTE(M36,".",""),":",","),60),IF(M36<1,CONVERT(M36,"day","mn"),M36)) [Did I get the parentheses and everything correct??] copy that into N29 [and S29 to test the decimal seconds scenario]. Note that I am assuming that you will revert N36 back to its original 1:04.6 entry. If you want to do the Find-Replace outside of the formula, then that portion becomes DOLLARDE(M36,60) without the SUBSTITUTE() functions.

    A thought on the question with Bo_Ry's formula. Bo_Ry's formula relies on Excel correctly interpreting the text 1:04.6 input as a valid time input and then converting it to a suitable serial number (1 minute 4.6 seconds is 0.00074769 days), which Bo-_Ry's formula then converts from days to seconds. My guess is that something in your Excel installation (maybe the decimal separator question?) prevents Excel from correctly interpreting the 1:04.6 text string, and the remainder of the math operations fail when this text to number/time conversion fails.

  26. #26
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Cool Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Quote Originally Posted by MrShorty View Post
    My guess is that something in your Excel installation (maybe the decimal separator question?) prevents Excel from correctly interpreting the 1:04.6 text string, and the remainder of the math operations fail when this text to number/time conversion fails.
    Dear MrShorty,

    Your last statement applies especially, this actually seems to be part of the problem. Thankfully, before I found the time to delve into the problem of installtion issues, my data provider offered to re-do the entries. So, hopefully the problem will be solved in an unexpected way! I am most grateful to you and the others who repiled, for devoting so much thought, that I will mark this as solved and of course add to your reputation. I have nevertheless learned a lot. Thank you!

    Best regards,
    Excel_Arate
    Last edited by Excel_Arate; 08-30-2020 at 02:02 PM.

  27. #27
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    That is good news. Were you able to give some ideas for data entry (all entries in decimal seconds? all entries as fraction of a day? or other consistent format?) so the provider knows exactly how you want to receive the data?

  28. #28
    Registered User
    Join Date
    05-25-2017
    Location
    Buchs, Switzlerand
    MS-Off Ver
    2019
    Posts
    75

    Re: Converting minutes, seconds and milliseconds to a decimal value in seconds

    Hi!
    The data is to be in decimal seconds. This will make life easier!
    Regards,
    Excel_Arate

+ 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. Converting minutes+seconds into decimal
    By Godssent2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2019, 09:25 AM
  2. Converting a sum from decimal minutes to minutes & seconds then multiplying by 10
    By sparkyliveson in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-22-2014, 11:53 AM
  3. RE: Converting decimal minutes to hours, minutes and seconds
    By mills49 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-14-2013, 09:14 AM
  4. Replies: 2
    Last Post: 03-16-2012, 06:51 AM
  5. minutes and seconds to seconds and milliseconds
    By nicolajohnson in forum Excel General
    Replies: 5
    Last Post: 06-21-2010, 09:29 AM
  6. Replies: 4
    Last Post: 01-18-2010, 04:29 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