Calculate Time Duration

I thought that this would have been a common problem but I haven't found anything similar to my issue.

For Row 5 starting with Column C and ending with column F, I entered in this data: 8.20 a 11.00 a. Basically I want to calculate duration of time by entering data such that the decimal is equivalent to ":". I.e. 8.20 = 8:20. Maybe there is a better way to solve this issue but I don't know how to subtract time notation if possible, i.e 11:00a - 8:20a = 2.67 hours. What my formula is doing is counting the 2 as a one-digit number rather than in the 10s place (2 instead of 20). Not sure how to amend that to where 8.20 = 500, 8.02 = 482.

Please Login or Register  to view this content.

Sorry if my code is a bit inefficient. Data is constantly being added to columns C through columns F.

Try this in cell G5:

Please Login or Register  to view this content.

Holy smokes. Thanks for the speedy response. They all work, I'll use your latest code, tigeravatar. Thanks, guys.

Wow, neverthought to use DOLLARDE. *Mind Blown*

Originally Posted by daddylonglegs

Perhaps there won't be any times around midnight but what if C5 = 12.3 and D5 = a signifying half past midnight, that formula treats the time like 12:30 PM

I suggest this version

=((TEXT(E5*100,"0\:00 ")&F5)-(TEXT(C5*100,"0\:00 ")&D5))*24

You do provide a good point about 12:30am for C5 and D5. That just made me realize something else. If C5=11.3 and D5=p while E5=1.3 and F5=a, it provides the hours "owed" instead of the actual hours. Not, that I couldn't solve this but there is probably a more efficient way than formulating it with an if function similar to the one shown below...

Please Login or Register  to view this content.

Basically if the value is negative, it adds 24 hours to make it correct or leaves it as is if the difference is already positive. It seems a bit redundant the way I did it...

Yep, answer should be 2, daddylonglegs. You guys are super formula experts. I have never seen half the functions you guys are using in the formulas. Thank you, works like an amulet.