Want to calculate decimal time from minutes/seconds

[Maytree]

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

[bryanbak3]

hello,
you can split the seconds into a new column so they would be easier to work with. then you could divide them by sixty individually without messing up your minutes. otherwise excel will try to convert your numbers into a time serial number and it will drive you crazy.

[bryanbak3]

what i was trying to say is that if you use any time related functions with those numbers it will try to convert them into a coded number that represents time in excel. the method i suggested would be overall easier because you can continue to just treat them as numbers.
hth

[Maytree]

hello,
you can split the seconds into a new column so they would be easier to work with. then you could divide them by sixty individually without messing up your minutes. otherwise excel will try to convert your numbers into a time serial number and it will drive you crazy.

Thanks for the reply, but that's exactly what I'm trying to get around doing. As I said in my original post, "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."

Are you saying that Excel does NOT have the power to do this at all?

[Maytree]

what i was trying to say is that if you use any time related functions with those numbers it will try to convert them into a coded number that represents time in excel. the method i suggested would be overall easier because you can continue to just treat them as numbers.
hth

How about the second option I listed: Give excel the number as 74:12 without any specific value attached, then tell it to split the input on either side of the column, treat each half differently, then recombine the results to obtain a new value? Possible?

[Pete_UK]

If you enter 74:12 into a cell, Excel will automatically assume that you mean 74 hours and 12 minutes. There are two ways around this - you could enter it as 0:74:12 or as 74:12.0, but in either case you have to type in two extra characters. An alternative is to use a helper column to derive the number of minutes correctly - if your entry is in A1 then you can use this formula and format the cell as General (or Number):

=A1*24

This will give you the answer 74.2, which is the number of minutes (and seconds) expressed as a decimal value. You can use this value to multiply by your charging rate expressed in dollars per minute.

Another way is to pre-format the cell as text, and then you can enter 74:12, but to convert this into the correct minutes and seconds you will need to use:

=LEFT(A1,2) + RIGHT(A1,2)/60

and you must ensure that you always type two digits followed by the colon (or any other character) followed by two digits.

Hope this helps.

Pete

[MrShorty]

As bryanbak said, the difficulty with giving Excel anything that looks like a date or time is that it stores that as a number that represents number of days since 1 Jan 1901.

I tried putting 74:12 into Excel, and it decided I meant 74 hours and 12 minutes -- which it stores as 3.something days

Would you be willing to enter it as 0:74:12 (0 hours, 74 minutes, 12 seconds)? This Excel will store as a number that is a fraction of a day. You can then simply multiply by 24*60 (make sure this cell does not assume a time format which Excel might do on its own) to convert the fractional day into fractional minutes.