Converting time into tenths of an hour (with a twist).

I want to preface this by saying that I am a bit of an amateur at Excel. What I am trying to do is track billable hours into tenths of an hour. I want to be able to subtract an ending time from a starting time and get the difference in tenths of an hour. Below is a picture of how this should look after using the formula {{=ROUNDUP((G2-F2)*24,1)}} where G2 is Time Stop and F2 is Time Start.

picture-2-full.JPG

Now here is the tricky part... I practice law in Tennessee and the administration of the courts in TN has broken down tenths of an hour not into every 6 minutes but instead into their own idea of tenths of an hour. See how they break it down in this chart below:

TN Hours.jpg

So, the problem becomes that if I were to put 8:00 as my starting time and 8:07 as my ending time then the formula I have will give me two tenths of an hour (.2). However, for Tennessee the roundup to the next tenth of an hour should not occur until 8:09, so I should actually be only getting one tenth of an hour (.1) when I work 7 minutes or 8 minutes, and of course that applies to every time I work 13-14, 19-20, etc. Basically they have extended their range an extra two minutes each tenth of an hour and I need to compensate for that.

How can I correct this formula to compensate for Tennessee's irregular fluctuation of tenths of an hour? IF statements with ranges or is there some easier way? Any help would be greatly appreciated and I hope I have explained this well enough. Thank you.

Just change it to Round instead of Roundup and trick the formula a little bit to =ROUND((G2-F2)*24,1) that should do the trick.

Hope I help

Originally Posted by jroques

Just change it to Round instead of Roundup and trick the formula a little bit to =ROUND((G2-F2)*24,1) that should do the trick.

Hope I help

Jroques, thanks for the quick reply. I think that works for every amount of time except for 1-2 minutes. Technically for 1-2 minutes it should round up and give me 1 tenth of an hour, but with just Round it will round that down to 0.0. However, I doubt there will be many times when I rack up just 1 or 2 minutes. So, I don't think it is worth stressing over just those unlikely scenarios, especially when I can just spot those and fix them myself. Thank you again for your quick reply and for the help you have given me. It is greatly appreciated.

Try this fix for the 2 minute scenario

=IF(G2-F2<=TIME(0,2,0),0.1,ROUND((G2-F2)*24,1))