PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

Is there a simple formula to convert time in hundredths to minute

 
 
Ron
Guest
Posts: n/a
 
      4th Jan 2010
I get an exported excel file that has the time in hours/hundredths and I have
to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
I'm doing the conversion but it takes me about 4 passes - is there a simple
way to convert this kind of time. Thank You
 
Reply With Quote
 
 
 
 
Eva
Guest
Posts: n/a
 
      4th Jan 2010
Can you tell us more about it? I don't understand why 6.18 = 6:11 or 6.15 =
6:09.
--
Greatly appreciated
Eva


"Ron" wrote:

> I get an exported excel file that has the time in hours/hundredths and I have
> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> I'm doing the conversion but it takes me about 4 passes - is there a simple
> way to convert this kind of time. Thank You

 
Reply With Quote
 
 
 
 
Bernie Deitrick
Guest
Posts: n/a
 
      4th Jan 2010
Ron,

You have a number of options, depending on what you need. The simplest is to format a cell for
time, and in the cell use a formula like

=A2/24

Where A2 has the 6.18 value.

HTH,
Bernie
MS Excel MVP


"Ron" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I get an exported excel file that has the time in hours/hundredths and I have
> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> I'm doing the conversion but it takes me about 4 passes - is there a simple
> way to convert this kind of time. Thank You



 
Reply With Quote
 
Fred Smith
Guest
Posts: n/a
 
      4th Jan 2010
So you want to convert decimal hours to an Excel time? If so, you want:
=a1/24
Format as time.

Regards,
Fred

"Ron" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I get an exported excel file that has the time in hours/hundredths and I
>have
> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> I'm doing the conversion but it takes me about 4 passes - is there a
> simple
> way to convert this kind of time. Thank You


 
Reply With Quote
 
David Biddulph
Guest
Posts: n/a
 
      4th Jan 2010
It's pretty simple, Eva.
6.15 is 6 hours and 15 hundredths.
There are 60 minutes in an hour, so 15 hundredths of an hour is 0.15*60 = 9
minutes, hence 6.15 is 6:09

The answer to Ron's question is that the way to do the conversion is =A1/24,
and format the result in a time format to suit your preferences.
--
David Biddulph

"Eva" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can you tell us more about it? I don't understand why 6.18 = 6:11 or 6.15
> =
> 6:09.
> --
> Greatly appreciated
> Eva
>
>
> "Ron" wrote:
>
>> I get an exported excel file that has the time in hours/hundredths and I
>> have
>> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
>> I'm doing the conversion but it takes me about 4 passes - is there a
>> simple
>> way to convert this kind of time. Thank You



 
Reply With Quote
 
Roger Govier
Guest
Posts: n/a
 
      4th Jan 2010
Hi Ron

Excel stores it's times as fractions of a day (24 hours)
Format the cell as hh:mm
then with 6.15 in cell A1 =A1/24


--
Regards
Roger Govier

"Ron" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I get an exported excel file that has the time in hours/hundredths and I
> have
> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> I'm doing the conversion but it takes me about 4 passes - is there a
> simple
> way to convert this kind of time. Thank You
>
> __________ Information from ESET Smart Security, version of virus
> signature database 4743 (20100104) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>


__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com



 
Reply With Quote
 
Joe User
Guest
Posts: n/a
 
      4th Jan 2010
"Ron" wrote:
> I get an exported excel file that has the time in
> hours/hundredths and I have to convert it to
> hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.


Well, the "simple" answer is: =A1/24

with the Custom format [h]:mm .

But 6.18 will display as 6:10, not 6:11. The reason is: 60*0.18 = 10.8
sec, and generally, Excel truncates seconds when displaying with an hh:mm
format [1].

So if you really want 6:11, you should do:

=ROUND(A1*60,0)/1440

where 1440 is 60*24.

-----
Endnotes

[1] I said that Excel "generally" truncates seconds when displaying hh:mm.
Actually, it sorta-rounds seconds, then truncates. So 6:10:59 (hh:mm:ss) is
displayed as 6:10 (hh:mm), but 6:10:59.51 is displayed as 6:11. I say "sorta
round" because 6:10:59.5 is displayed as 6:10.
 
Reply With Quote
 
Tbazemore
Guest
Posts: n/a
 
      4th Jan 2010
Roger,
I have attempted using =A1/24, but its not working.... maybe im putting it
in the wrong place? is it possible to elaborate a little more on the "how
to"? I have also been attempting this coversion for my monthly report.

Thanks.


"Roger Govier" wrote:

> Hi Ron
>
> Excel stores it's times as fractions of a day (24 hours)
> Format the cell as hh:mm
> then with 6.15 in cell A1 =A1/24
>
>
> --
> Regards
> Roger Govier
>
> "Ron" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I get an exported excel file that has the time in hours/hundredths and I
> > have
> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> > I'm doing the conversion but it takes me about 4 passes - is there a
> > simple
> > way to convert this kind of time. Thank You
> >
> > __________ Information from ESET Smart Security, version of virus
> > signature database 4743 (20100104) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >

>
> __________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

 
Reply With Quote
 
Fred Smith
Guest
Posts: n/a
 
      4th Jan 2010
You need to change A1 to whatever cell your hours are in. As you did not
specify any cell addresses, it's impossible for us to know where your data
is. So we use A1 as a sample.

If you still need help, tell us where your data is (ie E1, A27:A46), and
we'll give you a specific formula.

Regards,
Fred

"Tbazemore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Roger,
> I have attempted using =A1/24, but its not working.... maybe im putting it
> in the wrong place? is it possible to elaborate a little more on the "how
> to"? I have also been attempting this coversion for my monthly report.
>
> Thanks.
>
>
> "Roger Govier" wrote:
>
>> Hi Ron
>>
>> Excel stores it's times as fractions of a day (24 hours)
>> Format the cell as hh:mm
>> then with 6.15 in cell A1 =A1/24
>>
>>
>> --
>> Regards
>> Roger Govier
>>
>> "Ron" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > I get an exported excel file that has the time in hours/hundredths and
>> > I
>> > have
>> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
>> > I'm doing the conversion but it takes me about 4 passes - is there a
>> > simple
>> > way to convert this kind of time. Thank You
>> >
>> > __________ Information from ESET Smart Security, version of virus
>> > signature database 4743 (20100104) __________
>> >
>> > The message was checked by ESET Smart Security.
>> >
>> > http://www.eset.com
>> >
>> >
>> >

>>
>> __________ Information from ESET Smart Security, version of virus
>> signature database 4743 (20100104) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>


 
Reply With Quote
 
Tbazemore
Guest
Posts: n/a
 
      4th Jan 2010
Okay... turns out I actually have the hours and minutes in cell A1 thru a15.
I actually need the hour/hundredth)... Is there a formula? and if so, for A1
where should I be putting this formula?

Tabatha
"Fred Smith" wrote:

> You need to change A1 to whatever cell your hours are in. As you did not
> specify any cell addresses, it's impossible for us to know where your data
> is. So we use A1 as a sample.
>
> If you still need help, tell us where your data is (ie E1, A27:A46), and I need to covert it to hours/hundredths
> we'll give you a specific formula.
>
> Regards,
> Fred
>
> "Tbazemore" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Roger,
> > I have attempted using =A1/24, but its not working.... maybe im putting it
> > in the wrong place? is it possible to elaborate a little more on the "how
> > to"? I have also been attempting this coversion for my monthly report.
> >
> > Thanks.
> >
> >
> > "Roger Govier" wrote:
> >
> >> Hi Ron
> >>
> >> Excel stores it's times as fractions of a day (24 hours)
> >> Format the cell as hh:mm
> >> then with 6.15 in cell A1 =A1/24
> >>
> >>
> >> --
> >> Regards
> >> Roger Govier
> >>
> >> "Ron" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > I get an exported excel file that has the time in hours/hundredths and
> >> > I
> >> > have
> >> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
> >> > I'm doing the conversion but it takes me about 4 passes - is there a
> >> > simple
> >> > way to convert this kind of time. Thank You
> >> >
> >> > __________ Information from ESET Smart Security, version of virus
> >> > signature database 4743 (20100104) __________
> >> >
> >> > The message was checked by ESET Smart Security.
> >> >
> >> > http://www.eset.com
> >> >
> >> >
> >> >
> >>
> >> __________ Information from ESET Smart Security, version of virus
> >> signature database 4743 (20100104) __________
> >>
> >> The message was checked by ESET Smart Security.
> >>
> >> http://www.eset.com
> >>
> >>
> >>

>
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
System time(minute) change event doesn't raise per minute. =?Utf-8?B?THVja2Nhbw==?= Microsoft Dot NET Compact Framework 1 5th Jul 2007 04:49 PM
convert time from 60 minute hour to 100 minute hour =?Utf-8?B?SmJvZXJkaW5n?= Microsoft Excel Misc 2 6th Jul 2005 11:30 PM
Creating a chart to convert hundredths to minutes dvonj Microsoft Excel Discussion 2 13th Mar 2005 07:56 PM
convert 100 minute hour to a 60 minute hour =?Utf-8?B?VG9kZA==?= Microsoft Excel Worksheet Functions 1 15th Nov 2004 07:14 PM
To convert minute into hour and minute =?Utf-8?B?THljbw==?= Microsoft Excel Misc 2 27th Nov 2003 07:16 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:59 PM.