Conversion of Hr:min format to decimal of hour
Conversion of Hr:min format to decimal of hour
(OP)
Could someone help me with understanding how to convert from h:mm (hours:minutes)format in excel to 0.00 hours? I am able to export infomation from accounting software, but the billable hours can only be exported in h:mm format. I would like to extract specific information and total hours in excel, but the format would have to be converted first.
Thanks in advance for any advice.
Thanks in advance for any advice.





RE: Conversion of Hr:min format to decimal of hour
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
Thus, for example, if the cell C3 shows 13:55 it actually contains the value 0.579861... (days). Change the cell's format to "General" to see this. Alternatively, or additionally, create a cell to its right in which you put the formula "=24*C3" and you will have the value in hours. Again, you might have to change the format of this new cell before you see it the way you want to see it.
RE: Conversion of Hr:min format to decimal of hour
I suspect that means that you have a TEXT STRING and not a time at all.
The cheap and easy way to tell is to look at where the characters are in the cell.
Text is left justified, numbers and real excel times are right justified.
If you do have text, then you need
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,LEN(A1)-FIND(":",A1))/60
RE: Conversion of Hr:min format to decimal of hour
I did try MintJulip's equation but it returned a "value" error. However, this might be due to the following little wrinkle.
The issue that still confuses me is that I can't seem to execute the simple formula (+A1*24) in an adjacent cell to the reference cell. The formula simply shows up as text in this spreadsheet. My guess is that there could be a switch that might have been turned off when the accounting report was converted to a spreadsheet.
Does anyone have any advice on this issue?
Thanks to all for your input.
RE: Conversion of Hr:min format to decimal of hour
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
RE: Conversion of Hr:min format to decimal of hour
Really....seriously....plus sign ....equal sign....makes no diff. The cell will not pick up the reference. I haven't run across not being able to do this before.
Any advice?
RE: Conversion of Hr:min format to decimal of hour
Another possibility, though remote, is that the sheet is optioned for R1C1 reference style, which would be changed in the Options|General pane.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
Also checked the R1C1 switch as you suggested and the box is not checked.
RE: Conversion of Hr:min format to decimal of hour
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
Here is a piece of the spreadsheet for review.
Thanks again for the help.
RE: Conversion of Hr:min format to decimal of hour
Go to cell you want to insert the formula. Only type in =M3. Hit return. You should see the fractional hour value. Edit the cell to multiply by 24 or whatever. Hit return. You should now see the converted value.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
RE: Conversion of Hr:min format to decimal of hour
i did IR's solution ... "=M3" gave me "0:45" in custom format. ok, clicked number format and got "0.03" *24 = 0.45; which is i think want you wanted.
puzzled why M3 shows "0:45" but the cell contains "12:45 AM". it seems to be reading the duration as a time of the day ? ... reformat the M cells as numbers, this'll give you duration as a fraction of a day.
maybe reformat M cells, insert a column, = M*24, copy (not cut) and paste (as values) the new column over the top of column M, delete the added cloumn; talk to whoever gave you this to see if they'll save you this data massage.
RE: Conversion of Hr:min format to decimal of hour
I think I missed a setting of the destination cell format to "Number" prior to to doing "=M3" since I had messed around with the file for a bit. The cells in N and O are text-formatted. Not sure why changing the format to number didn't work the first time around.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Conversion of Hr:min format to decimal of hour
0:45 in accounting format refers to 45 minutes and we want it to be translated to 0.75 hrs. IRstuff's advice was what we were trying to do within the original data limits, which for some strange reason only returns text when referencing a cell. Inserting columns within these limits didn't work either. Still don't understand that.
However, if we choose a column from outside the original data limits, it seems to reference cells from from inside the data set properly.
This appears to be an Excel quirk, where there seems to be a setting (switch) that gets turned off upon importing the data from the accouting software. Now that we have a work around....we're very happy.
RE: Conversion of Hr:min format to decimal of hour