Conversion of Hr:min format to decimal of hour 1

[LobstaEata]

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.

 

[IRstuff]

The internal date format is a floating point number whose integer part represents the number of days since Jan 1, 1901, and whose fractional part is the fraction of the day since the previous midnight. If you take the fractional part and multiply by 24, you get the floating point value of the number of hours since midnight.

TTFN

FAQ731-376

 

[Denial]

If your cells are actually FORMATTED as time (which you are suggesting they are), then the VALUE that they contain is expressed as a decimal fraction of a day.

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.

 

[MintJulep]

billable hours can only be exported in h:mm format

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

 

[LobstaEata]

Denial's method makes sense to me. If the h:mm format is changed to General and the value is multiplied by 24, it does result in the correct hours with a decimal.

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.

 

[IRstuff]

perhaps inserting an equal sign first?

TTFN

FAQ731-376

 

[GTstartup]

zing

 

[LobstaEata]

Very cute IR.....have you thought of a life of comedy? I did get a chuckle out of the comment though ... all things "equal" and all.

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?

 

[IRstuff]

It probably means that the cell is formatted for text. I would try to find a working cell and use the Format Painter to copy the working cell's formats to the bad cell.

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

 

[LobstaEata]

That's what I thought at first as well, but the cell value can be coverted from h:mm to General format, so if it were tect. that wouldn't be possible. The cell value just can't seem to be referenced from another cell.

Also checked the R1C1 switch as you suggested and the box is not checked.

 

[IRstuff]

Can you post the sheet?

TTFN

FAQ731-376

 

[LobstaEata]

Sure.

Here is a piece of the spreadsheet for review.

Thanks again for the help.

 

[IRstuff]

OK, it's one of those funky problems with an inaccessible text format.

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

 

[LobstaEata]

Tried this in the "L" column and it just returned text "=M", but then I tried it in a cell outside the original spreadsheet limits and it referenced the cell properly. Looks like we're a go. Thanks again for your help IR.

 

[rb1957]

a couple of things ...

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.

 

[IRstuff]

Those cells have a custom format that is a stripped-down time format. Without the date, the default time serial number is 0.03125, which displays as 12:45 AM in the edit mode.

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

 

[LobstaEata]

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.

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.

 

[jghrist]

I've had several instances where I've imported data and the worksheet ends up with cells that have a text format for numbers and formulas. I've fixed it by using ASAP utilities.