Decimal to Minutes

Is there a format or formula to convert a decimal to time?  An example would 
be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The Workbook 
is relatively large so any method would have to easily apply to thousands of 
numbers, versus simply manually converting one cell at a time.



Regards,

Gary


0
allge (63)
8/16/2007 1:01:43 AM
excel 39879 articles. 2 followers. Follow

6 Replies
496 Views

Similar Articles

[PageSpeed] 20

It's easier than you think Gary, since 1 is one day in Excel which is 24 
hours, so one hour is 1/24

just divide by 24

=A1/24

where A1 holds 0.5

then the important thing is to format the cell with the formula as either 
hh:mm:ss or [hh}mm:ss  (the latter allows for displaying more than 24 hours)

Same goes for the other way around if you have Excel time format and wants 
to calculate  an amount using an hourly rate

=A1*24*hourly_rate

here it is equally important to format result as currency or number



-- 

Regards,

Peo Sjoblom





"Gary" <allge@cox.net> wrote in message 
news:OyTpdF63HHA.4584@TK2MSFTNGP03.phx.gbl...
> Is there a format or formula to convert a decimal to time?  An example 
> would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The 
> Workbook is relatively large so any method would have to easily apply to 
> thousands of numbers, versus simply manually converting one cell at a 
> time.
>
>
>
> Regards,
>
> Gary
>
> 


0
terre081 (3244)
8/16/2007 2:05:07 AM
Assuming your decimal time is in column A and begins with A1,
custom format column B as [h]:mm
and then in B1 enter  =A1/24

..5 should then show as 0:30,  1.75 as 1:45, and so on.

If you want your  time to show as 0h 30m and 1h 45m instead,
then custom format  column B as [h]"h"" "mm"m"

--
Alan


"Gary" <allge@cox.net> wrote in message 
news:OyTpdF63HHA.4584@TK2MSFTNGP03.phx.gbl...
> Is there a format or formula to convert a decimal to time?  An example 
> would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The 
> Workbook is relatively large so any method would have to easily apply to 
> thousands of numbers, versus simply manually converting one cell at a 
> time.
>
>
>
> Regards,
>
> Gary
>
> 

0
8/16/2007 2:36:02 AM
I think that  [hh}mm:ss in your reply below must be a typo.
Perhaps you meant to write [hh]:mm:ss  ??
--
Alan


"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:u5fakn63HHA.4880@TK2MSFTNGP03.phx.gbl...
> It's easier than you think Gary, since 1 is one day in Excel which is 24 
> hours, so one hour is 1/24
>
> just divide by 24
>
> =A1/24
>
> where A1 holds 0.5
>
> then the important thing is to format the cell with the formula as either 
> hh:mm:ss or [hh}mm:ss  (the latter allows for displaying more than 24 
> hours)
>
> Same goes for the other way around if you have Excel time format and wants 
> to calculate  an amount using an hourly rate
>
> =A1*24*hourly_rate
>
> here it is equally important to format result as currency or number
>
>
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
>
>
>
>
> "Gary" <allge@cox.net> wrote in message 
> news:OyTpdF63HHA.4584@TK2MSFTNGP03.phx.gbl...
>> Is there a format or formula to convert a decimal to time?  An example 
>> would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The 
>> Workbook is relatively large so any method would have to easily apply to 
>> thousands of numbers, versus simply manually converting one cell at a 
>> time.
>>
>>
>>
>> Regards,
>>
>> Gary
>>
>>
>
> 

0
8/16/2007 2:42:24 AM
Works great,
Thanks Peo...


"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:u5fakn63HHA.4880@TK2MSFTNGP03.phx.gbl...
> It's easier than you think Gary, since 1 is one day in Excel which is 24 
> hours, so one hour is 1/24
>
> just divide by 24
>
> =A1/24
>
> where A1 holds 0.5
>
> then the important thing is to format the cell with the formula as either 
> hh:mm:ss or [hh}mm:ss  (the latter allows for displaying more than 24 
> hours)
>
> Same goes for the other way around if you have Excel time format and wants 
> to calculate  an amount using an hourly rate
>
> =A1*24*hourly_rate
>
> here it is equally important to format result as currency or number
>
>
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
>
>
>
>
> "Gary" <allge@cox.net> wrote in message 
> news:OyTpdF63HHA.4584@TK2MSFTNGP03.phx.gbl...
>> Is there a format or formula to convert a decimal to time?  An example 
>> would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The 
>> Workbook is relatively large so any method would have to easily apply to 
>> thousands of numbers, versus simply manually converting one cell at a 
>> time.
>>
>>
>>
>> Regards,
>>
>> Gary
>>
>>
>
> 


0
allge (63)
8/16/2007 3:25:30 AM
Obviously


Peo


"*alan*" <in_flagrante@hotmail.com> wrote in message 
news:kQOwi.1209$i75.1004@newssvr19.news.prodigy.net...
>I think that  [hh}mm:ss in your reply below must be a typo.
> Perhaps you meant to write [hh]:mm:ss  ??
> --
> Alan
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message 
> news:u5fakn63HHA.4880@TK2MSFTNGP03.phx.gbl...
>> It's easier than you think Gary, since 1 is one day in Excel which is 24 
>> hours, so one hour is 1/24
>>
>> just divide by 24
>>
>> =A1/24
>>
>> where A1 holds 0.5
>>
>> then the important thing is to format the cell with the formula as either 
>> hh:mm:ss or [hh}mm:ss  (the latter allows for displaying more than 24 
>> hours)
>>
>> Same goes for the other way around if you have Excel time format and 
>> wants to calculate  an amount using an hourly rate
>>
>> =A1*24*hourly_rate
>>
>> here it is equally important to format result as currency or number
>>
>>
>>
>> -- 
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>>
>>
>> "Gary" <allge@cox.net> wrote in message 
>> news:OyTpdF63HHA.4584@TK2MSFTNGP03.phx.gbl...
>>> Is there a format or formula to convert a decimal to time?  An example 
>>> would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The 
>>> Workbook is relatively large so any method would have to easily apply to 
>>> thousands of numbers, versus simply manually converting one cell at a 
>>> time.
>>>
>>>
>>>
>>> Regards,
>>>
>>> Gary
>>>
>>>
>>
>>
> 


0
terre081 (3244)
8/16/2007 3:26:48 PM
It depends on what format you want the results. If you simply want .5 to 
become 30 then multiply by 60.

However, if you want .5 to be formatted in minutes then it is .5 divided by 
24 and then you have to format the cell to minutes.

To apply a multiplication or division to multiple cells, enter a value in 
any spare cell and copy it. Select the range where you want it applied and 
Paste Special and select Multiply or Divide etc. This operation ignores text 
in the paste to selection.

Tip: Make a backup of your workbook before applying these operations.

Regards,

OssieMac

"Gary" wrote:

> Is there a format or formula to convert a decimal to time?  An example would 
> be .5 would be 30 (minutes), .75 would be 45 (minutes), etc.  The Workbook 
> is relatively large so any method would have to easily apply to thousands of 
> numbers, versus simply manually converting one cell at a time.
> 
> 
> 
> Regards,
> 
> Gary
> 
> 
> 
0
OssieMac (238)
8/17/2007 12:54:36 AM
Reply:

Similar Artilces:

Missing Decimal Places
Help please, Today when I open any excel spreadsheet all my decimals (.) have disappeared and been replaced by a space. If I go and add them in they recognize them as a period, not a decimal and I get value errors. Anyone know what has happened and how to fix it? Try Start / Control Panel / regional and language options / (Correct region??) Hit customise and then on the Regional options tab / make sure decimal separator is not a space and is actually a period . -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ...

Format a cell to display decimal hours.
How do I format a cell to display decimal hours? Instead of displaying 3:45 (for 3 hours, 45 minutes), which is one of the standard formats, I'd like to display 3.75 (hours). h.hh doesn't do it. The above is formatting for an elapsed time calculation (finish time minus start time). (On a "time card") Excel 2000. Will a later Excel do it for me? Some good source for a parsing function? Any good reference? Thanks, Fred Holmes Fred, Format the cell in #.## format and multiply your result by 24. http://HelpExcel.com "Fred Holmes" wrote: > How do I forma...

Decimal alignment
Five data points 75.5 13.4 34.4 90.8 9.3 The column of cells is centered. Is there a way to align the 9.3's decimal with the above data points? Thanks in advance. The only way I know is to convert them to text and then enter a space s everything aligns properly. Michae -- Message posted from http://www.ExcelForum.com Format the cells to have a set number of decimal places. If you type 9.3 and press Enter, it will display as 9.30. Why were numbers centered in the first place. A number format with decimal would make them line up. Format, cells, number, ... or custom &quo...

how do i change hours and minutes into a decimal number?
...

Query returning Int instead of Decimal Value ?
Hi, I am using Sql Server 2008, Why the following Query returning the Answer : 3 When it should return : 32.692 SELECT CAST(32692/1000 AS decimal(12,5)) Kindly adv. Thanks and Regards, Luqman You are dividing two numbers of type int (32692 and 1000) so the result is an int, namely 32. Then you case that as a decimal with 5 decimal places and you get 32.00000. To fix, make at least one of your numbers a decimal before you divide, either by putting a decimal point on one of them or casting one of them as decimal. SELECT CAST(32692./1000 AS decimal(12,5)) o...

Safe mode starts instantly, normal mode takes 5 minutes
Anybody got any ideas why this huge slowdown has happened. I'm suspecting Bluetooth, but I've uninstalled everything I possibly can and it still takes 5 minutes to start up. However if I start in safe mode (/SAFE on the command line) it starts instantly. I have no com add-ins enabled, no 3rd party toolbars, no nothing, but something is still slowing down the startup. It uses up all available memory in the process of starting up as well. This is Outlook / Office 2003 Pro, with all the latest updates. No matter what settings I choose for Advanced memory management it still ends up...

VPN keeps dropping at the 3 minute mark
Hi, Our main office runs SBS 2003 R2 and the standard VPN server. All our remote offices, except one, have no issue with connecting to the VPN and everything works fine. The one office which does have an issue manages to connect but then after approx 3 minutes the connection drops and they have to reconnect. As you can imagine this becomes very frustrating and means that accessing files on the network becomes virtually impossible. I've tried using my own machine on their network and I experience the same problem as they do, so I know it's not related to their computer. ...

Adding 1st recipient to appointment takes 2/3 minutes!
Hi, Gradually over the last month or so we have noticed it takes longer and longer to add a first recipient(any user) to the FIRST appointment you create AFTER opening outlook 2000 either in the morning or after just closing and re-opening outlook. I have moved both logs to a new scsi array with mirrored 15k u320 disks in case it was a disk IO issue(which we did have a minor issue with anyway) but still no difference in the issue I mention above. I also notice that the schedule + connector throws up an error when attempting to look at it "the object has been deleted, pres f5 to up...

Using military time but with 100 minutes not 60 minutes
Is it possible to convert regular time into military time. But instead of using 60 minutes using 100 minutes. Ex. typing in 14.30 and it changes to 14.50(which is using 100 minutes.) after you click enter or go to the next cell. I use this in my work to help figure easier how many hours my drivers have worked. Hi Time is stored in Excel as fractions of a day. Multiply your time value by 24, and it will turn to decimal so 14:30 will be 14.5 Regards Roger Govier srwamp wrote: > Is it possible to convert regular time into military time. But instead of > using 60 minutes using 1...

Get $500 To Your PAYPAL Account In Just A Minutes
Do you need some extra money right now? Would you be interested to get $500 daily Paypal Money through some genuine tricks from home internet ? Get Money Online for FREE! Get started in the next few minutes without FEES..... Yes Absolutely ... This TRICK is absolutely FREE for all..... See the FORM for details on how to get $500 to your account. Due to some Premises I Have Hidden the FORM in an IMAGE ...... CLICK on the IMAGE which is BELOW the SEARCH BOX http://www.webinfos4all.euro.st ...

number formatting
Is there a way to make a custom number format so that decimals are rounded to a certain number of digits, but numbers with no decimals are shown without the decimal point. i.e. if I use the format 0.## or #.##: 8.237 will show as "8.24" but 8 will show as "8." whereas I want 8 to show as "8" Is this possible? Thanks. On Fri, 14 Oct 2005 12:32:12 -0400, "Ric" <riiiiiic@hotmail.com> wrote: >Is there a way to make a custom number format so that decimals are rounded >to a certain number of digits, but numbers with no decimals are shown...

Breaking Report into Minute Detail
I'm working on breaking a report down by subgrouping A, then B, then C however, subgroup C has even a finer detail which needs to be grouped by hours - H, then by Salary - S and lastly by Dollar amounts - D. I can get it broke down to the H level but even with multiple IIF statements I can't group the data according to the user's requirements. I was thinking that I could use multiple queries to separate this data out, but am unsure how to use multipe queries within the report without using sub reports. I am a novice to VBA and am trying to accomplish this with the functionality ...

Send 900 Billion valid Emails in Just 5 Minutes Only!
...

decimal places 01-30-08
Hi I have changed the number of decimal places to 2 in the database and in on the form and the decimal places still does not show. I tried to enter numbers with decimals directly in the database and on the form and the result is the same, each round up to the nearest whole number. What do I need to do? -- Thank-you! Ruth "Ruth" <Ruth@discussions.microsoft.com> wrote in message news:406EFCF4-1B09-4E36-982A-2D0FE7112DC7@microsoft.com... > Hi > > I have changed the number of decimal places to 2 in the database and in on > the form and the decimal places sti...

Calculate Hour & Minute
Hi all, How do i get total hour and minute between two date and time i.e between 1130 am on 10/12/2003 and 8.45 pm on 13/12/2003. tq. Measured in hours and minutes? Just subtract the smaller from the larger and give it a custom format of: [h]:mm or [hh]:mm faeiz2 wrote: > > Hi all, > > How do i get total hour and minute between two date and time i.e between > 1130 am on 10/12/2003 and 8.45 pm on 13/12/2003. > > tq. -- Dave Peterson ec35720@msn.com Yes in hours & minutes. How about the different dates? let say I want to know how many hours I work from 1 d...

Decimal Place Problem via Export
When converting a report into an excel file for email , it somehow shows the 2 decimal places on the excel spreadsheet - even though I had indicated within the report that the Format=Standard Decimal=0 I eventually wanted to create a format like 200,000 but it's giving me 200,000.00 I can manually strip the 2 decimal places in excell but do not want the user to play with this functionality. Could someone assist. Try using the Format() function. Iin your control source of the field on your report, use something like: "=Format([myNumber],######) See Access help file for Forma...

How do I have excel save a copy every _ minutes ONLY in the even.
How do I have excel save a copy every _ minutes ONLY in the event of a crash? If you're using xl2002 or higher, turn on AutoRecovery. It actually saves a copy of the workbook and will recover from a crash (excel or windows). (From a previous post:) But xl2k (and previous versions) offers an autosave function--used it to save your files at regular timed intervals. As an alternative, you may want to consider using Jan Karel Pieterse's addin called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And wh...

Formatting cells for decimals and hyphens
Greetings, I'm trying to make a spreadsheet of available guns at our store and I need to type in things like: ..410 ..300 ..308 ..243 30-06 but it self corrects .410 to .41 and if I write 10-11-11 it wants to create a date. How do I turn off the formatting so I can type in decimals and hyphens without excel correcting them to some other format? Thanks, Mark Preformat that range (whole column?) as text. (Format|cells|number tab|Text) Or start your data entry with an apostrophe: '.410 '30-06 Kram wrote: > > Greetings, > > I'm trying to make a spread...

formatting to have minutes > 59
I'm opening a HTML file (containing a table) in Excel 2002. The file opens ok, but I have a problem with formatting a column which has minutes and seconds only, but that the minutes can be greater than 60. E.G the html file has values for this field of say: 1278:00 02:38 82:01 (in other words 1278 minutes and 32 seconds, 2 minutes and 38 seconds,...) However Excel displays this as: 1278:00:00 2:38 82:01:00 I think Excel is assuming that if the number is > 60 then it can't be minutes so it must be hours, and therefore the field is HHHH:MM:SS. When I try to format the cells, (sa...

Conditional formatting : amount of decimals
Conditional formatting should be extended for the amount of decimals. One should be able to set the conditional formatting before entering the numbers so the number entered in a cell is immediately rounded to the set amount of decimals. Example : If a number is lower than 1 use 2 decimals If a number is between 1 and 10 use 1 decimal If a number is greater than 10 use no decimal If 0.1234 is entered, Excel changes it in 0.12 If 1.234 is entered, Excel changes it in 1.2 If 12.34 is entered, Excel changes it in 12 The changes occur immediately after entering the number. This should be made...

Subtracting military times (decimal format)
I apologize if this has been asked & answered many times before, bu after reading for days in this forum, I can not find a direct answer t my needs. I have timecards that are already displayed in military/decimal format I do not want to convert to hours/minutes and I'm not interested i displaying results in hh:mm format. I do not want the date displayed. simply want to calculate each employees total time worked for thei shift's "start" to "end" (keeping the decimal format). Results are no rounded...employees are paid for all fractional hours. There are no se...

center aligning decimals in excel column
Would anyone know how to center align by decimal a column of numbers. I've not had a bit of luck figuring this one out... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You could use: 1. Select the column of numbers 2. Choose Format>Cells 3. On the Number tab, select the Custom category 4. In the text box, type: ??????.?????? (use an equal number of ? on each side of the decimal point) 5. Click OK jphilip wrote: > Would anyone know how to center a...

rounding to nearest five minutes
I'm a total newby with trying to use Excel (or any spreadsheet for that matter) and I need a simpy rounding process but can only find things for rounding to 15 minute intervals or else always rounding up or down. Is there a formula to simply round :02 down to :00 and :03 up to :05? I apologize if this is right under my nose and I'm just too stupid to find it. ;-) Tim One way: XL stores times as fractional days, and there are 288 5-minute periods in a day, so: =ROUND(A1*288,0)/288 Format as time if necessary In article <3K6Th.21695$hO2.2327@edtnps82>, "T...

How to format without decimal point unless significant digits?
Is there a custom format string that will format a number so that the decimal point is displayed *only* if there are non-zero digits to the right? I tried #,##0.####, which works great except for integers. "12345" formats as "12,345." I'd like to get "12,345" But I want to get the decimal point and any non-zero digits to the right of the decimal point. Here are some examples: Before After 0.123456 0.1235 5.34 5.23 25 25 /* no decimal point */ 1,234.56789 1,234.5679 "Jenni...

Running a macro every five minutes
Hi I need help to combine the two macros below. Further down the line i will be hooking Excel up to a real time dat feed for financial data.I would need to run a macro at five minut intervals. However i can't run it exactly on the 5 minute interval eg:-18:55:00,19:00:00,19:05:00,becuse i need to run it maybe 20 second after the five minute intervals to allow the data to be posted in th data feeds database,eg:-18:55:20,19:00:20,19:05:20 The first macro below is for timing and the second macro AUTOINTRADA is the macro i need to run every five minutes. I'm not sure how to combine ...