+ Reply to Thread
Results 1 to 9 of 9

Converting mixed numbers into time intervals

  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Converting mixed numbers into time intervals

    Hello,

    I have a column of mixed numbers that represents how long it takes my company to do a task. I'm trying to come up with a formula that looks at the decimal of the mixed number and converts it to the nearest .25 interval so that I know how much time to charge for doing that task. Is there any formulas that look at decimal places?

    Examples of what I'm trying to do:
    3.15 > 3.25
    5.6 > 5.5
    3.5 > 3.5
    18.2 > 18.25
    1.4 > 1.5
    3.5 > 3.5
    1.4 >1.5
    2.8 > 2.75

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

    Re: Converting mixed numbers into time intervals

    You get the divisor like this:

    =MOD(A1,1)
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting mixed numbers into time intervals

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

    v A B
    1 3.15 3.25
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Converting mixed numbers into time intervals

    I'm currently making a long IF(and( statement with the mod formula.

    I tried the dollarde, and it didn't convert to the correct intervals for other mixed numbers

    example:
    5.6 6.00
    3.5 3.83
    18.2 18.33
    1.4 1.666666667
    3.5 3.833333333
    1.4 1.666666667

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting mixed numbers into time intervals

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

    v A B
    1 3.15 3.25
    2 5.6 5.5
    3 3.5 3.5
    4 1.4 1.5
    5 2.8 2.75
    6 18.2 18.3

  6. #6
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Converting mixed numbers into time intervals

    This formula brute forces it, but is sub optimal

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Converting mixed numbers into time intervals

    Quote Originally Posted by AlKey View Post
    How about this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 3.15 3.25
    2 5.6 5.5
    3 3.5 3.5
    4 1.4 1.5
    5 2.8 2.75
    6 18.2 18.3
    This seems optimal. Thanks!

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

    Re: Converting mixed numbers into time intervals

    How does the result of 18.3 fit the brief?

  9. #9
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Converting mixed numbers into time intervals

    I think that cell is just rounded for him, it shows 18.25 for me

+ 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. Replies: 2
    Last Post: 07-22-2014, 09:20 AM
  2. Converting Data in Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 02:42 AM
  3. Need to Transpose Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2011, 08:30 PM
  4. Converting time stamps to time intervals
    By enhydra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2011, 04:51 PM
  5. Excel 2007 : Converting Numbers into Time
    By asheysp in forum Excel General
    Replies: 3
    Last Post: 02-25-2011, 08:17 PM
  6. Converting Numbers to Time
    By Smonczka in forum Excel General
    Replies: 11
    Last Post: 05-31-2005, 01:05 PM
  7. Converting Numbers to Time
    By MB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-18-2005, 11:11 PM

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