Convert a number to a time

I have the following number 232.5 which is 232 hours and 0.5 of an hour. I 
want to convert this to 232 hours and 30 minutes or 232:30. How can i do 
this? I know i need to multiply the decimal by 60, but how can i perform a 
calculation on just the decimal part of a number and not the whole number?
0
Jack4963 (284)
1/19/2006 8:12:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1084 Views

Similar Articles

[PageSpeed] 49

Depending what you wish to do excel stores times as well as dates as
numbers, with 1 being a full day. An option is to divide everything by
24 and use a custom format of dd hh:mm although this will express the
hours as days and hours

It depends what you want to do really


-- 
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
View this thread: http://www.excelforum.com/showthread.php?threadid=502807

0
1/19/2006 8:27:00 AM
suppose you have 232.5 in cell A1. in cell B1 type
=((A1-ROUNDDOWN(A1,0))*60/100)+ROUNDDOWN(A1,0)

That should do the trick.

Steven.

"Jack" wrote:

> I have the following number 232.5 which is 232 hours and 0.5 of an hour. I 
> want to convert this to 232 hours and 30 minutes or 232:30. How can i do 
> this? I know i need to multiply the decimal by 60, but how can i perform a 
> calculation on just the decimal part of a number and not the whole number?
0
Steven (151)
1/19/2006 8:35:04 AM
Divide by 24 and format the resultant cell as [h]:mm

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jack" <Jack@discussions.microsoft.com> wrote in message
news:F0857D9D-48DD-4BD4-B613-25D2D3FC1225@microsoft.com...
> I have the following number 232.5 which is 232 hours and 0.5 of an hour. I
> want to convert this to 232 hours and 30 minutes or 232:30. How can i do
> this? I know i need to multiply the decimal by 60, but how can i perform a
> calculation on just the decimal part of a number and not the whole number?


0
bob.phillips1 (6510)
1/19/2006 8:45:16 AM
Thanks Bob. I played around with custom formats, but could not get it right. 
This works perfectly.

"Bob Phillips" wrote:

> Divide by 24 and format the resultant cell as [h]:mm
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "Jack" <Jack@discussions.microsoft.com> wrote in message
> news:F0857D9D-48DD-4BD4-B613-25D2D3FC1225@microsoft.com...
> > I have the following number 232.5 which is 232 hours and 0.5 of an hour. I
> > want to convert this to 232 hours and 30 minutes or 232:30. How can i do
> > this? I know i need to multiply the decimal by 60, but how can i perform a
> > calculation on just the decimal part of a number and not the whole number?
> 
> 
> 
0
Jack4963 (284)
1/19/2006 9:08:16 AM
Reply:

Similar Artilces:

Converting Crystal report from MSCRM 1.2
I have a client who has upgraded to V3.0 for CRM. They want to continue using Crystal for their reporting engine because they have 40 custom reports. Does anyone know how to easily accomplish this? I have created a new connection to the new CRM Views in SQL, but I can't seem to figure out how to easily replace all the fields that are currently in the report using the SSO connection to my new connection. Any help would be greatly appreciated. Thanks, Amy many of the services such as rpttosql.com say they will only convert so far. some even say they will only get the general lay...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

Odd Looking Number
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I l...

Numbers turn red when equals to 0
Good day, Im monitoring our stock inventory using excel, i just want to know if I can make a certain cell turn red if my inventory is >5 or equal to zero.? Thanks -- shiela21cute ------------------------------------------------------------------------ shiela21cute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33169 View this thread: http://www.excelforum.com/showthread.php?threadid=529926 Check out Conditional formatting Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum...

How to convert US date format to European
My computer is set up to use the European style dates (ddmmyyyy), not the USA-style dates (mmddyyyy format). So if I enter "01/06/05" I get a date of 01-June-2005. However when I download some information in CSV format at times that same date is formatted like this: "06/01/05". However this is interpreted as 06-January-2005 in the spreadsheet. How can I convert the US formatted values to European, in effect swapping the first two numbers? Thanks T Meisner -- 1025 Osborne Rd., New Germany, NS, Canada B0R 1E0 Rename the .csv file to .txt File|open that .txt file...

Detailed Time Sheet (overtime, comp time, vacation used)
I need to track in time, out time, in time, out time, overtime, vacation totals (received/used), comp received, comp used, and so. I would recommend to write all the headers on row 1, and start entering the data on row 2. Any column that is a calculation, enter a formula (for example, if column E is the difference of column D and C, enter "=D2-C2") and format the cells as you want the data to be presented (date/time, number, etc.) Hope this helps, Miguel. "Robert D. Sandersfeld" wrote: > I need to track in time, out time, in time, out time, overtime, vacation ...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Convert Time
Hello, In one cell I have the time showing (2:45:59 PM). In another cell I would like to input a formula that will convert the time into a text cell showing military time. Thanks in advance for the help. Depends on what you call "military time" Anyway, in the new cell, insert the formula: =A1 assuming that the original time is in A1 Then format custom as hh:mm:ss or hhmmss -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim" <Jim@discussions.microsoft.com> wrote in message news:5A87A835-1AEE-4276-85DD-59C7152DBB91@microsoft....

Date/Time field calculation
Hello, i am trying to make a sum calculation on a field that keeps duration of time. How can i do this? I get errors or zero as result. Thanks On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote: > Hello, > i am trying to make a sum calculation on a field that keeps duration of > time. How can i do this? I get errors or zero as result. > Thanks You need to do the calculation at a level at least 1 level below what you want the answer to be in. Example msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00 PM#) will give an ans...

Rotating page numbers
I have a document with both page layouts (landscape and portrait) however the page numbers obviously will be pointing in the direction of the associated layout does anyone know how to be consistent in this? ...

Time formula
I'm trying to find a fomula that inserts the time on column b when I enter a text on column a.... ...

Change Directory Creation/Modification Date/Time programatically
Hi, Can anyone help me out to change the directory creation/modification date/time through C++/MFC code? Can this be acheived without changing the system time? Thanks in advance. Vignesh. In article <98EDAE87-3F31-4F06-AE8C-378CF91A6F3A@microsoft.com>, Vignesh@discussions.microsoft.com says... > Hi, > Can anyone help me out to change the directory creation/modification > date/time through C++/MFC code? SetFileTime. > Can this be acheived without changing the system time? Yes. -- Later, Jerry. The universe is a figment of its own imagination. "Je...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Time series #2
I have several tabs for different dates with data in it. Colomn contains the time in format 13:30:30. For my project, I require to know the number of data points in variou time series, pls see below. 7:30:00 8:30:00 8:30:00 9:30:00 9:30:00 10:30:00 10:30:00 11:30:00 11:30:00 12:30:00 12:30:00 13:30:00 13:30:00 14:30:00 14:30:00 15:30:00 15:30:00 16:00:00 I tried countif and if function but could not come up with the results PLS HELP. If I have to do it manually then I am dead..... -- mohitmahaja ----------------------------------------------------------------------- mohitmahajan's ...

Sequential numbering of invoices
I have recently downloaded an Excel template for invoices from Microsoft's website. Is there any way to fix it so that every time I create a new document based on this template, it automatically gives it an invoice number one greater than the previous invoice (eg, starting at 100 and then the next one I open would be 101, then 102 and so on)? http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards, Peo Sjoblom "Nick Xylas" <nickxylas@wmconnect.com> wrote in message news:1180551140.693535.260930@g4g2000hsf.googlegroups.com... >I have recently download...

Convert to time
How would I go about converting 8.00 to 8:00:00 AM in excel. Can this be done? Floyd Forbes Wrote: > How would I go about converting 8.00 to 8:00:00 AM in excel. > Can this be done? Hi Floyd If A1 contains 8.00 format A2 to Time, then input =(A1/24)+A1 oldchippy : -- oldchipp ----------------------------------------------------------------------- oldchippy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1990 View this thread: http://www.excelforum.com/showthread.php?threadid=57380 Divide by 24 and format as time. =a1/24 (8.50 will convert to 8:30 ...

Convert a number to a time
I have the following number 232.5 which is 232 hours and 0.5 of an hour. I want to convert this to 232 hours and 30 minutes or 232:30. How can i do this? I know i need to multiply the decimal by 60, but how can i perform a calculation on just the decimal part of a number and not the whole number? Depending what you wish to do excel stores times as well as dates as numbers, with 1 being a full day. An option is to divide everything by 24 and use a custom format of dd hh:mm although this will express the hours as days and hours It depends what you want to do really -- Dav --------------...

Convert adobe pagemaker files
Is there any way to convert Adobe Pagemaker files to Publisher or Word? Via the clipboard ...

same entry for second time
Hello i am new in the area and i try to write some lines but i need your help i work with 2003 version now , i have made a db , the part are : 1 table with 5 columns id,barcodes,pieces,weight,colour 1 same but with no data 1 qry of choose and 1 frm with composite barcode and pieces,weight,colour (the only variable is the pieces) wich i have connect the 4 columns with code lets say what i write a barcode when a write for 2 time the same bc i want to add the number of pieces this time to the previos entry Please i want the code pls help me friends the code i use is: Private Sub BARCODE_...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

find first number
How do I find the first number in a string? I need to update a field by removing anything that could not be a number. Unfortunately, there's no specific number of characters which I can use to start and stop. Intentions are to replace everything before the first number and after the last number. Fortunately, the numbers I want are grouped together, but in the missle of strings. thanks Here is one example: CREATE TABLE Foo ( foo_key INT PRIMARY KEY, foo_data VARCHAR(35)); INSERT INTO Foo VALUES (1, '1'); INSERT INTO Foo VALUES (2, 'aa21a'); ...

copy/paste into excel changes numbers into date format
I need to copy a lot of raw data from html format into excel. Most of the data consists of a min/max figure (eg. 31/45) Excel (2003) always converts this number to date format no matter what I format the cells as. I have been unsuccesful in converting back to the same format. Any help or advice is appreciated. Thanks Hi try formating the cell s as 'Text' before pasting the values -- Regards Frank Kabel Frankfurt, Germany Rufus wrote: > I need to copy a lot of raw data from html format into > excel. > > Most of the data consists of a min/max figure (eg. 31/45) &...

type of data convertion error
Hi all, when i'm trying to import or just to make a linked table i found errors in the some of the fields becaouse of the type of data whenever there is a mix in one column between some that are numbers and some that are chars the default is char but the numbers are becoming error how can i overcome the problem? ...

counting the number of times shown
I am trying to figure out a way to put a numerical value next to the id that is shown, suggestions? ex: ID This is what I am hoping to get to show up in the next column 55 1 55 2 37 1 46 1 62 1 55 3 89 1 82 1 Hi With ID in column A, insert this formula in B2 and copy it down as required: =COUNTIF($A$2:A2,A2) Regards, Per "klh84" <klh84@discussions.microsoft.com> skrev i meddelelsen news:14D13B5C-FEE8-4489-B91E-AC65F2ABB090@microsoft.com... > I am trying to figure out a way to put a numerical value next to the id > th...