Excel - converting time to actual hours 1

[drrep]

Hi,

Ok, I have a spreadsheet that has a cell that has a time entry (ex. 150:42:19) but in the formula bar, it's displayed as 06/01/2005 6:42:19 AM).

I want to convert that entry to actual hours.

I tried using =(A1-INT(A1))*24 where A1 is the entry but to no avail. I had gotten that off microsoft.com

Can anyone help me out? Thanks

 

[AnotherHiggins]

drrep,

Welcome to the wonderful world of MS Date/Time.

First - and this is very important -

[red]You do NOT want to change what you see in the formula bar!!![/red]​

Please read faq68-5827 (Why do Dates and Times seem to be so much trouble) for more info.

Basically, all dates are stored in Excel as whole numbers (based on the number of days since 1/1/1900) and all times are stored as decimals (based on a percentage of 24 hours). Anything over 24 hours is therefore stored as a whole number, or date.

You can change how the CELL is displayed, but not how the date/time shows up in the Formula bar. [sub](Actually, you *can* change that, but only by storing the date/time as text, which disallows you to do any math with it.)[/sub]

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,,

FIRST -- Why do Dates and Times seem to be so much trouble? faq68-5827

SECOND, do you want to CONVERT the underlying data from DAYS to HOURS

or

do you simply want to DISPLAY Hrs:min:sec -- change the format via Format/Cellsw/Number Tab - Custom [h]:mm:ss

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[drrep]

I want to display the actual hours, as in 27.5 for example

Presently, the format of the cell is custom [h]:mm:ss but I do not want to touch the format of the cell...

 

[AnotherHiggins]

I want to display the actual hours, as in 27.5 for example

Presently, the format of the cell is custom [h]:mm:ss but I do not want to touch the format of the cell...

I'm not clear; are you asking a question?

You will have to format the column to tell Excel how to display the time - it can't read your mind. But once you have formatted the column, you shouldn't have to do anything else.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

You can display FRACTIONS of a DAY in hour:minute:second format

If you want ONLY HOURS, then you need to convert DAYS to HOURS.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[AnotherHiggins]

It might help if you give us more details about the situation. Are you (or someone else) going to be manually entering times, are they being imported, is this a one-time analysis?

Maybe include an example of what the formula bar shows vs. what the cell is displaying vs. what you want the cell to display. Also, what you are going to do with these times.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[drrep]

As I mentioned in my very first post:

Ok, I have a spreadsheet that has a cell that has a time entry (ex. 150:42:19) but in the formula bar, it's displayed as 06/01/2005 6:42:19 AM).

I do not want to modify anything. This a value that was spit out from some application.

Without modifying the cell or the formula bar content, how can I transfer the 150:42:19 to actual hours (150.XXX)

 

[AnotherHiggins]

I urge you to remember that no one here can read your mind. I have no doubt that you understand what you mean, but please phrase your questions in full detail. In response to your original post: 150:42:19 is 'real hours'.

But in your last post you included "150.XXX". That's the clue that you left out before.

So you want to convert from minutes & seconds to decimals? That is easy. But you say that your time is displaying as 06/01/2005 6:42:19 AM in the formula bar? Are you sure it isn't displaying as 6/1/[red]1900[/red]? I ask because January 6 2005 6:42:19 AM, when formatted as [h]:mm:ss, displays as 920598:42:19.

I'll just assume that you typed the date incorrectly.

In that case, multiplying by 24 ought to do it. Just change the format of the resulting cell to General or Number.

Example: your original time (150:42:19) is in A1. In B1, type [blue]=a1*24[/blue] then format B1 as General or Number.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[drrep]

Sorry, that was in my 2nd post

"I want to display the actual hours, as in 27.5 for example

Presently, the format of the cell is custom [h]:mm:ss but I do not want to touch the format of the cell..."

And yes, that was a typo. It should be 06/01/1900 6:42:19AM

And thank you, it worked...I was doing that before but was not changing the format of the resulting cell....my bad

 

[KenWright]

drrep - notice the bottom left of any of the posts:-

'Thank xxx for this valuable post'

All it takes is a couple of clicks to say thank you and show that the question has been answered.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------