Convert Time to Decimal with Custom Time Matrix

I am working on a formula to convert flight time into tenths of a decimal but with a catch -there is always a catch, isn't there. The client has specific ranges for which to round up:

:00 - :02 = .0
:03 - :08 = .1
:09 - :14 = .2
:15 - :20 = .3
:21 - :26 = .4
:27 - :32 = .5
:33 - :38 = .6
:39 - :44 = .7
:45 - :50 = .8
:51 - :56 = .9
:57 - 1.02 =1.0

When I put my start time in D5 and finish time in D7, I want the total flight time with the above variables to be displayed in E5. I found some formulas that seemed like they were close, but I can't account for the non-standard rounding - the first group it's two min, the rest are 5 min groups.

Any ideas??

Looks like you want the results in fractions of an hour ?

Try
= MROUND((D7-D5)*24, 0.1)

Can we assume (as ChemistB did) that the numbers are true "time values" and not "text that looks like time"? If they are, it seems to me that even the basic ROUND() function should do this =ROUND((D7-D5)*24,1). There is nothing unusual, as far as I can tell, about the "rounding table" you have listed there.

Good Point Mister S. :::slaps forehead:::

I am on a PC and it worked the same as your MAC. Format the cell with time to include Seconds and see if it's different than your data. Maybe

=ROUND(MROUND(D7-D5, "0:01:00"+0)*24, 0.1)

Hello again, I've been using the formula and everything was good until someone found an anomaly. When I go through the times and check the formula it works as advertised. However, someone found that depending on the start time used, it skews the result by .1 - However, it's not predictable and makes the spreadsheet completely unreliable. To make matters worse, I had to move the spreadsheet to Google Sheets using the same formulas and it has the same problems. I sanitized the spreadsheet and cant figure out how to post it here for review and help.

Edit post dialog should be a "manage attachments" link below the composition window. Click on this link to bring up the upload manager (at least it does for me). I have not had trouble with this site's uploader, but it seems that others do, so hopefully that will work.

If it helps, what you are describing has echoes of the "penny off" type problems that accountants wrestle with or any of the other ways that "round-off error" inherent in all computer computations manifests itself. So, if you do manage to get a file uploaded, that is the first thing I will look for. Remember that time values in Excel are simply numbers/fractions. Since Excel cannot exactly represent these values, these errors can cause this kind of problem, and I would be checking to see if that is possibly a problem here.

Just as I suspected -- computer round off error.

What I don't understand is that each of the values are exactly 9 minutes apart or exactly 15 min apart

What you need to understand is that, to Excel, there is no such thing as times that are "exactly" 9 minutes apart, because 9 minutes (when expressed as a fraction of a day) will a repeating, non-terminating decimal in binary notation. This means that some "9 minutes" are seen by the computer as slightly larger than 9 minutes and some 9 minutes are slightly smaller (illustrated in attachment).

Solutions to this depend on the individual programmer and the exact nature of the problem. If I understand what you are trying to do here, I would probably simply add a small amount to each difference (1e-8 or 1 ms or something like that) before the round step. Something like MROUND((J3-I3+1e-8)...). This additional fraction will hopefully push anything infinitesimally close to 9 minutes (or other boundary value) over the boundary value so it rounds correctly, but still be small enough so that values well below 9 minutes will still round down.