# Converting mixed numbers into time intervals

1. ## 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. ## Re: Converting mixed numbers into time intervals

You get the divisor like this:

=MOD(A1,1)

3. ## Re: Converting mixed numbers into time intervals

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

 v A B 1 3.15 3.25

4. ## 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. ## Re: Converting mixed numbers into time intervals

Formula:
`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. ## 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. ## Re: Converting mixed numbers into time intervals

Originally Posted by AlKey
Formula:
`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. ## Re: Converting mixed numbers into time intervals

How does the result of 18.3 fit the brief?

9. ## Re: Converting mixed numbers into time intervals

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

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

#### 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