+ Reply to Thread
Results 1 to 11 of 11

Converting hours/min to decimal time

  1. #1
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Unhappy Converting hours/min to decimal time

    I've tried finding a way to solve my problem, however I've only been able to find a solution for if the time is set up as something like 1:30 (which would be 1.5). Unfortunately, the times are collected as a text string, so that each entry is "x hour(s) x mins". I tried to find and replace the words individually, but that hasn't had any success (the word wont replace for some reason).

    The other unfortunate part is that each field is not identical. If the contractor only worked 45 minutes, for example, it will say "45 mins", not "0 hours 45 min", so I can't do a consistent removal or substitute for each field because not every one says the same thing. What I have across fields is something like this;

    If there is only 1 hour, it will say "hour", else it will say "hours"
    If the work was less than 1 hour, there will only be "mins" and "hours" will not be present
    If the work was an even amount of hours, it will only say "hours" and "mins" will not be present

    So the fields are populated with an assortment of "hour" "hours" and "mins", making it hard for me to figure out how to remove them all, and convert it to a decimal place. Ultimately, I need 1 hour 30 min to = 1.5

    Is there any way to do this short of editing each individual field? There are hundreds, and I have to do this bi-weekly, so I was really hoping there'd be a way.

    Thanks in advance

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Converting hours/min to decimal time

    Please upload sample workbook with enough samples to demonstrate your issue and expected output.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Post Re: Converting hours/min to decimal time

    I think I uploaded it.

    The yellow fields are the ones I am trying to convert.

    The field in green is what I'm hoping the end result will look like, based on the entries in the HOURS_WORKED (L) column.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Converting hours/min to decimal time

    Try below...
    =IFERROR(LEFT(L2,SEARCH("hour",L2)-1)*1,0)+IFERROR(IF(SEARCH("mins",L2)<=4,LEFT(L2,3)/60,MID(L2,FIND("@",SUBSTITUTE(L2, " ", "@", 2)),LEN(L2)-SEARCH("mins",L2))/60),0)

    Sample attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Converting hours/min to decimal time

    Oh god.. That's an insane formula. It works, so thank you so much! I'd never have figured this out on my own.

    Very much appreciated! <3

  6. #6
    Registered User
    Join Date
    05-04-2017
    Location
    MALAYSIA
    MS-Off Ver
    2007
    Posts
    34

    Re: Converting hours/min to decimal time

    Option rev 0(hilited yellow):
    Try in column z=aa2+ab2
    column aa=iferror(mid(l2,search("hour",l2,1)-2,1),0)
    column ab=iferror(mid(l2,search("min",l2,1)-3,2),0)/60

    or the best option(hilited green):
    Directly type in column z=iferror(mid(l2,search("hour",l2,1)-2,1),0)+iferror(mid(l2,search("min",l2,1)-3,2),0)/60
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Converting hours/min to decimal time

    Both of these formulas are pretty crazy. I dunno what most of it even means, but thank you both.

  8. #8
    Registered User
    Join Date
    08-31-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Converting hours/min to decimal time

    I'm trying to do the inverse. I want to convert the number 90 into an h:mm format. I have an Excel “Things to Do” worksheet. I’m trying to create a formula for Column E that will give me the total time required to perform tasks. My current solution tells me 1.5 hours instead of 1:30. Here’s what I have. Any help will be appreciated.

    Row 1 is my Column headers
    A1 is =NOW()
    B1 is Things to do
    C1 is Time req'd in min
    D1 is Cumulative Time in min
    E1 is Hours Req'd

    Row 2 is a hidden row to make the formulas work and for sorting purposes.
    A2 is =NOW()
    B2 is 0 formula start row
    C2 is 0
    D2 is 0
    E2 is =TEXT(D2,"H:MM")

    Rows 3
    A3 is =A2+TIME(0,C3,0)
    B3 is Make dinner menu for the week
    C3 is 30
    D3 is =(C3+D2)
    E3 is =D3/60

    Rows 4
    A4 is =A3+TIME(0,C4,0)
    B4 is Make grocery list
    C4 is 15
    D4 is =(C4+D3)
    E4 is =D4/60

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Converting hours/min to decimal time

    To convert decimal minutes to time, divide by 1440 and format as [h]:mm
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Registered User
    Join Date
    08-31-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Converting hours/min to decimal time

    That worked. Thanks.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Converting hours/min to decimal time

    You're welcome.

+ 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 decimal to Month:Day:Hours:Minutes:Seconds
    By KellyK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2013, 03:34 AM
  2. Replies: 13
    Last Post: 05-03-2013, 08:42 PM
  3. [SOLVED] converting decimal hours to regular time but longer than 24 hours
    By rkruse in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2013, 07:45 PM
  4. [SOLVED] Decimal Time to Hours and Minutes over 24 hours in total
    By FlyingTiger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2012, 06:18 AM
  5. [SOLVED] Converting hours and minutes to decimal hours
    By Calandra@Seohan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-25-2012, 05:25 PM
  6. Converting individual cells within a range from minutes to decimal hours
    By epyzdrh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2008, 09:53 AM
  7. Converting a decimal time into hours and minutes
    By LizHough in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-04-2006, 06:10 AM
  8. converting a decimal number to hours and seconds
    By jeda67 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2005, 05:40 AM

Tags for this Thread

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