×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Conversion of Hr:min format to decimal of hour

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.

RE: Conversion of Hr:min format to decimal of hour

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: Eng-Tips.com Forum Policies

RE: Conversion of Hr:min format to decimal of hour

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.
 

RE: Conversion of Hr:min format to decimal of hour

Quote:

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

RE: Conversion of Hr:min format to decimal of hour

(OP)
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.

RE: Conversion of Hr:min format to decimal of hour

zing
 

RE: Conversion of Hr:min format to decimal of hour

(OP)
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?

RE: Conversion of Hr:min format to decimal of hour

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: Eng-Tips.com Forum Policies

RE: Conversion of Hr:min format to decimal of hour

(OP)
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.

RE: Conversion of Hr:min format to decimal of hour

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: Eng-Tips.com Forum Policies

RE: Conversion of Hr:min format to decimal of hour

(OP)
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.

RE: Conversion of Hr:min format to decimal of hour

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.  

RE: Conversion of Hr:min format to decimal of hour

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: Eng-Tips.com Forum Policies

RE: Conversion of Hr:min format to decimal of hour

(OP)

Quote (rb1957):

  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.

RE: Conversion of Hr:min format to decimal of hour

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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources