Hi ---

I've tried to do this in a couple of ways I found on the Internet but I can't seem to get it to work right, and I don't know why.

I'm a medical transcriptionist and I bill some of my clients by the tape-minute. That is, a set fee per minute of tape transcribed. I usually express the duration of the transcription in minutes:seconds, such as 35:23 or 74:12 or whatever. (Note that I don't use hours:minutes: seconds -- just minutes:seconds.)

For billing purposes I want to calculate the decimal equivalent of, say, 43 seconds so I can bill for it properly. (Yeah, the difference between billing for 43/100 and 43/60 seconds isn't much, but over the course of fifty transcriptions those lost seconds add up!) I want to figure out how to make my invoicing sheet calculate the proper fee for the work by automatically figuring the seconds billed decimally, and I can't figure out how to do it. I can do it manually, of course -- just by putting the seconds in a different column and then manually dividing by sixty and adding those decimal seconds back into the total minutes in order to figure the line item -- but it's a nuisance and I THINK Excel has the power to do it automatically for me. I just can't figure out what I'm doing wrong.

I've tried using the HOUR/MINUTE/SECOND function but I never get the right answer. First of all, when I put in, for example, 74:12, it thinks I mean 74 hours and 12 minutes, not 74 minutes and 12 seconds. I'm not sure how to get around this. First, if I put in 00:74:12 into the cell, it spits back "0.051527778", without any operand in the cell. I don't know what it thinks I'm telling it to calculate here. If I set the cell format specifically to HH:MM:SEC and put in 74:12, again with no operand, it spits back "2:12:00", which, again is not remotely correct. (It should be figuring 74:12 as 1:14:12, shouldn't it?) In the function bar it says "1/3/1900 2:12:00 AM". None of the "time" formats appears to give the correct value here, because what I'm looking for is a DURATION, not a time stamp. If I manually put in 1:14:12 (meaning 1 hour, 14 minutes, 12 seconds) the function bar says 1:14:12 AM. Setting the format to mm:ss or [h] mm:ss under the "Custom" cell format doesn't give back the right values either. So first question is: Where is the correct cell format for what I want to do?

If we ignore the question of getting the cell to properly accept the input of 74:12 as "74 minutes, 12 seconds" and just treat 74:12 as a general text input with no specific formatting, is there a function in excel to use to split the data on either side of the colon delimiter and have Excel treat each half of the data separately and then recombine them? That is, a function to tell it "Look at the number to the left of the colon and leave it alone; look at the number to the right of the colon, divide it by sixty, and then add it to the number on the left of the colon and give me back the final value which I will then use to calculate my fee properly." I could live with this without having to figuring out how to force Excel to recognize 74:12 as "74 minutes 12 seconds."

Thanks for any help!
-- Maytree