Remember Me?

Menu

#1
December 28th 06, 01:23 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 1
Converting decimal fractions of minutes to seconds

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#2
December 28th 06, 01:45 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 196
Converting decimal fractions of minutes to seconds

Hi Ron

How do you get 12 secs from 0.5 of a minute?

If you have a decimal value eg in A1 of 5.5 and this number represents
your minutes, then you could use a formula like:

=A1/1440

and format the cell as mm:ss to get the format as eg 05:30 (use
hh:mm:ss or maybe [h]:mm:ss if you will have numbers of more than 60
minutes).

Does this help?

Richard

ronnie-g wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#3
December 28th 06, 01:53 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,365
Converting decimal fractions of minutes to seconds

This will get you 5:12 from 5.5, assuming 5.5 is in A1

=LEFT(A1,FIND(".",A1)-1) & ":" & 60 / RIGHT(A1,LEN(A1)-FIND(".",A1))

I think I see where .5 comes up with 12 minutes (1/5 minute vs 1/2 minute).
If that was a typo then this would give you 5:30
=LEFT(C568,FIND(".",C568)-1) & ":" & 60 *
RIGHT(C568,LEN(C568)-FIND(".",C568)+1)

Note that both results are text, not true time.

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#4
December 28th 06, 02:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058
Converting decimal fractions of minutes to seconds

If 5.5 is in cell A1 then:

=.000656565*A1 and format as mm:ss
--
Gary's Student

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#5
December 28th 06, 03:32 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 5
Converting decimal fractions of minutes to seconds

Sorry... Question should have read: How do I convert 5.5 minutes to display
5:30 (5 minues 30 seconds).

"RichardSchollar" wrote:

Hi Ron

How do you get 12 secs from 0.5 of a minute?

If you have a decimal value eg in A1 of 5.5 and this number represents
your minutes, then you could use a formula like:

=A1/1440

and format the cell as mm:ss to get the format as eg 05:30 (use
hh:mm:ss or maybe [h]:mm:ss if you will have numbers of more than 60
minutes).

Does this help?

Richard

ronnie-g wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#6
December 28th 06, 03:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 5
Converting decimal fractions of minutes to seconds

Sorry..... Question should have read: How do I convert 5.5 minutes to
display 5:30(5 minues 30 seconds).
I successfully used your first example : =LEFT(C568,FIND(".",C568)-1) & ":"
& 60 * RIGHT(C568,LEN(C568)-FIND(".",C568)+1)
Thanks!

"JLatham" wrote:

This will get you 5:12 from 5.5, assuming 5.5 is in A1

=LEFT(A1,FIND(".",A1)-1) & ":" & 60 / RIGHT(A1,LEN(A1)-FIND(".",A1))

I think I see where .5 comes up with 12 minutes (1/5 minute vs 1/2 minute).
If that was a typo then this would give you 5:30
=LEFT(C568,FIND(".",C568)-1) & ":" & 60 *
RIGHT(C568,LEN(C568)-FIND(".",C568)+1)

Note that both results are text, not true time.

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#7
December 28th 06, 03:41 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 5
Converting decimal fractions of minutes to seconds

Sorry..... Question should have read: How do I convert 5.5 minutes to display
5:30(5 minues 30 seconds).
I successfully used your first example : =LEFT(C568,FIND(".",C568)-1) & ":"
& 60 * RIGHT(C568,LEN(C568)-FIND(".",C568)+1)
Thanks!

"RichardSchollar" wrote:

Hi Ron

How do you get 12 secs from 0.5 of a minute?

If you have a decimal value eg in A1 of 5.5 and this number represents
your minutes, then you could use a formula like:

=A1/1440

and format the cell as mm:ss to get the format as eg 05:30 (use
hh:mm:ss or maybe [h]:mm:ss if you will have numbers of more than 60
minutes).

Does this help?

Richard

ronnie-g wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#8
December 28th 06, 03:42 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 5
Converting decimal fractions of minutes to seconds

Sorry..... Question should have read: How do I convert 5.5 minutes to display
5:30(5 minues 30 seconds).
I successfully used example : =LEFT(C568,FIND(".",C568)-1) & ":" & 60 *
RIGHT(C568,LEN(C568)-FIND(".",C568)+1)
Thanks!

"Gary''s Student" wrote:

If 5.5 is in cell A1 then:

=.000656565*A1 and format as mm:ss
--
Gary's Student

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#9
December 28th 06, 04:10 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 174
Converting decimal fractions of minutes to seconds

Don't know why you'd want to do that when Richards suggestion is so much
simpler,

=C568/1440

format as [m]:ss

or if you want a text result

=TEXT(C568/1440,"[m]:ss")

"ronnie-g" wrote:

Sorry..... Question should have read: How do I convert 5.5 minutes to display
5:30(5 minues 30 seconds).
I successfully used example : =LEFT(C568,FIND(".",C568)-1) & ":" & 60 *
RIGHT(C568,LEN(C568)-FIND(".",C568)+1)
Thanks!

"Gary''s Student" wrote:

If 5.5 is in cell A1 then:

=.000656565*A1 and format as mm:ss
--
Gary's Student

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

#10
December 28th 06, 05:20 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,365
Converting decimal fractions of minutes to seconds

And I agree with that - much simpler, more straightforward. I only attacked
it from the text aspect because of his initial typo'd example (and carried
that on to the text preparation for 5:30 to keep from jumping track and
confusing him).

ronnie-g, if you need true time, Richard's way is the way to go, you can set
format of the cell up to get it to show just 5:30 (with those numbers) vs
12:05:30 as it may try to do.

"daddylonglegs" wrote:

Don't know why you'd want to do that when Richards suggestion is so much
simpler,

=C568/1440

format as [m]:ss

or if you want a text result

=TEXT(C568/1440,"[m]:ss")

"ronnie-g" wrote:

Sorry..... Question should have read: How do I convert 5.5 minutes to display
5:30(5 minues 30 seconds).
I successfully used example : =LEFT(C568,FIND(".",C568)-1) & ":" & 60 *
RIGHT(C568,LEN(C568)-FIND(".",C568)+1)
Thanks!

"Gary''s Student" wrote:

If 5.5 is in cell A1 then:

=.000656565*A1 and format as mm:ss
--
Gary's Student

"ronnie-g" wrote:

How do I convert 5.5 minutes to display 5:12 (5 minues 12 seconds).
Thanks!
--Ron

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post youngxxi Excel Worksheet Functions 4 August 6th 06 06:06 PM LizHough Excel Worksheet Functions 1 July 4th 06 11:00 AM LondonLion Excel Worksheet Functions 3 February 10th 06 07:17 PM jeda67 Excel Worksheet Functions 1 December 4th 05 10:35 AM Patricio Boric Excel Worksheet Functions 1 November 28th 04 08:43 PM

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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2004-2017 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"

Copyright © 2017