Time formatting #3

Hi
Just a quick question one of my calculations returns a length of tim
in hours in a decimal format. I was wondering if there was a way t
format the cell so that for example if the figure 53.5 was returne
then the cell would display the value 53:30
(for 53 hours 30 minutes). the best that i can get so far is using "
?/60" to return:
53 30/60
This isn't as neat as i would like. I have tried the date/tim
formatting options but they only seem to return things like 12:00:00.
Any ideas

--
dzi
-----------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1042
View this thread: http://www.excelforum.com/showthread.php?threadid=46779

0
9/15/2005 10:20:14 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
427 Views

Similar Articles

[PageSpeed] 34

Don't think it can be done as the decimal part refers to parts of 60, not
parts of 10/100/1000 etc, so it needs a conversion, which isn't a natural
facet of formatting.

-- 
 HTH

Bob Phillips

"dziw" <dziw.1ved6c_1126782307.1628@excelforum-nospam.com> wrote in message
news:dziw.1ved6c_1126782307.1628@excelforum-nospam.com...
>
> Hi
> Just a quick question one of my calculations returns a length of time
> in hours in a decimal format. I was wondering if there was a way to
> format the cell so that for example if the figure 53.5 was returned
> then the cell would display the value 53:30
> (for 53 hours 30 minutes). the best that i can get so far is using "#
> ?/60" to return:
> 53 30/60
> This isn't as neat as i would like. I have tried the date/time
> formatting options but they only seem to return things like 12:00:00.
> Any ideas?
>
>
> -- 
> dziw
> ------------------------------------------------------------------------
> dziw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=10422
> View this thread: http://www.excelforum.com/showthread.php?threadid=467798
>


0
bob.phillips1 (6510)
9/15/2005 11:14:49 AM
I do not think there is any format, but you can create something like
it. If you assume the result of your calculations is in cell A1, then
In cell A2 write:
=INT(A1)&":"&ROUND((A1-INT(A1))*60;0)
In stead of "A1" you can write your calculation, but then it can get a
little complicated

NSV


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

0
9/15/2005 12:30:43 PM
Reply:

Similar Artilces:

conditional formatting
I want to format column I (highlight cell) under the following condition the value in same row column F is > 0 and the cell in column I is empty. I have put the following code in the formula bar for conditional formatting and I'm getting an error. How do I fix the problem? First I select column I, and then I select conditional formatting. Then I change "cell value is" to "formula" and enter the following in the formula box. =F1>0 and I1 = "" When I click OK, I get an error. What is the error? Place the cursor in I1 cell and pres...

Time question
I need a formula that would figure out what time it would have to be to complete a 40 hour week The information I have to plug in is this. Current total hours - 37 hrs 32 mins Current time is - 12:39 pm What time does it have to be to complete 40 hours? Seems like it should be simple but.... -- gueyo A1: 37:32:00 B1: 40:00:00 C1: 12:39 PM D1: =C1+(B1-A1) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "gueyo" wrote: > I need a formula that w...

CRM 3.0 Reports:Error when trying to open reports on client m/c
I am unable to access CRM Reports from any other machine except the CRM Server (Physical) box. I logged in as different users on the CRM server and was able to open and see all of the reports based on roles. But I am not able to view reports from any of the client machines. I am accessing Reports through a web-client. Upon trying to access reports through a client machine, I get the message " An error has occurred. For more information, contact your system administrator". This does not tell me anything I do not see anything unusual in the Web server (CRM Server), or the Da...

Visio #3
How do I print grid lines using Visio. there is an example for vba code at John Marshall's site www.mvps.org/visio/vba al "Jay" <jay.werelius2@verizon.net> wrote in message news:F08177E5-1576-4CED-A1B7-D2201AA60F5E@microsoft.com... > How do I print grid lines using Visio. Due to popular demand, Visio 2003 now gives you the option to print gridlines! Tag "Al Edlund" <edlund@ameritech.net> wrote in message news:eC49eIkrDHA.2416@TK2MSFTNGP10.phx.gbl... > there is an example for vba code at John Marshall's site > www.mvps.org/visio/vba > &...

Excel Time Problem
I exported a file from a telephone switch reporting system as a "tab separated text" file, because when I export the data in Excel I have to deal with merged cells and other formatting problems. I have a couple of columns that have time values like 160:02:05 for logged in time, and 34:16:46 as the waiting dime� Both are time �DURATIONS� or time in a state. I used the Format, Cell, custom and [ss] to get the duration value to seconds, and I get 123406 and 576125 respectively. I then use these numbers in a calculation typically like 1338 / (576125 - 123406) and I get 219 as the an...

Signatures #3
Hi, I have been asked to find a way to automatically insert a signature into Outlook (for all our 600 clients) The signature is a gif or jpeg file with the persons name & address ... I've been looking in Outlook so far and it seems a bit long to do it yourself, even to make a procedure for the clients they won't be able to do it! Is there anyway on an Exchange Server 2000 to automatically insert a signature into an email (Gif or jpeg or any other) ? Only problem is that the signatures are all different!! Could somebody please help me or just tell me it isn't possible!!! :-((( ...

Date/Time Modified
Hello, My Access Database consists of many tables. The Query attached to my main Client Information Form is made up of 4 tables. -Client Table -Account Table -Batch No Table -Advisor Table Basically, I want to be able to insert a Date/Time Modified record at the top of my main form that will show a data modified if any record is changed/updated in any of these tables. Is this possible? Thank you, Mellissa -- Message posted via http://www.accessmonster.com ...

Excel 2007 dates and conditional formats
Can anyone please tell me why I can not use a conditional format (<>=) to identify if a date is greater or smaller then another. Everytime I try it, Excel does it right in the first column but misses by 2 days in all following. It also appears to have problems with the years. Example: Date of propposed compleation of a task is 10/10/07; if the date of actual completion is 10/10/07 or sooner, turn GREEN. If the date of completion is 10/11/07 or older, turn RED. Can anyone help?? Regards Mary Hi Mary, First thing to check: What does 10/11/07 mean? November 10 1907? November 10 2007? O...

Error in posting 4 times! Frustrating problems with html signatures and styles (normal.dot)
I just learned that the message posted 4 times; I thought I had canceled the previous posts where I had not filled in the right newsgroups.... On my machine, it only shows posted 1 time; but I guess that the news server does not support cancellations of posts. Sorry about that...... ...

default mail format based on domain
Greetings - After 20_ years of Eudora and Thunderbird (in that order), I'm looking at Outlook for the first time (we're moving to an Exchange backend, so...worth a look, for obvious reasons). In Thunderbird, if I want to set a default HTML format for emails sent to a certain domain, I can specify that. For any other domain(s), plain text. Is there an easy way to do that in Outlook (I'm using 2007)? Basically, I want emails sent to my enterprise domain (i.e., my place of employment) to be in HTML or perhaps RTF, but mail sent to other domains to be plain text only. Again, I want ...

5.6.3 error
We have a monitoring program sending us daily reports for all our servers. It's been running fine until we just hit a "magic" numbers of how many servers we monitor. The email sent is in xml format, including 3 .png images per server, so I guess we just passed 250 in total images, which are considered attachments. How can I up the number of allowed attachments in Exchange? Can't find this setting anywhere...! TIA!! / Per Wouldn't it be better to put that stuff up on a web page? I've never heard of such a limit; you must really be pushing the envelope. You...

vlookup problem #3
I am trying to a vlookup on formatted text. I I had a long string and extracted certain characters from it using the "mid" function. Now if do a vlookup on the extracted list it doesnt work. But if I were t actually type in the values in the extracted list the vlookup start working. I tried changing the format of the extracted list to general number and text..nothing works. Can anybody please tell me what I am doing wrong? I know my vlookup i correct because it works when i type the values in the table array. thank -- Message posted from http://www.ExcelForum.com Check to be ...

parameter values not valid #3
keep getting a window each time I try and save a new task (Outlook 2000) it says: "Could not complete the operation. One or more parameter values are not valid" I just set up Outllook, and can't figure out why it would do this. ...

kill formatting brought over from reports
When I export a report from various sources (crystal reports or an Access Database that has an option to send to Excel), the worksheet format buttons stop working, or work erratically depending on something that must be coming over. Also, sometimes I get a sheet sent to me from someone who has done weird formatting, and it overrides my buttons (where the $ sign button produces some custom symbol, for example). I can copy the detail and paste to another worksheet, where the buttons will work, but is there a way to tell Excel to ignore this type of menu change when it receives a spreadsh...

Evey time I create a new document and save it, it defaults to template
I am using excel 2003. Evey time I create a new document and save it, it defaults to template - xlt. To create a document I just do new -> blank document. How can i fix this? thanks Tools>Options>Transition. Save Excel Files As. Change to MS Excel Workbook. Gord Dibben MS Excel MVP On Fri, 5 Feb 2010 17:54:26 -0600, "Greg" <greg@nospam.com> wrote: >I am using excel 2003. >Evey time I create a new document and save it, it defaults to template - >xlt. >To create a document I just do new -> blank document. > >How...

Incompatible file format.
Have been using Money for years and have Money Deluxe 2007 installed on both my home (Vista) machine and my notebook (XP) machine. Problem is when I go on the road and take my Money file with me, my XP machine says that the file is incompatible and was created with a newer or updated version of Money. Both machines have the same version and are both include the latest updates. What gives? Did Microsoft create something in Vista that precludes using the Money file under any other operating system? I may have to switch to Quicken or even a Linux version if they keep this up.............

CRM 3 - User Transfered All Cases to Own Name
We had a user that left the company. Another user transfered all of his cases (resolved / active) to his name. He some how reassigned all the resolved and active cases in the DB to his own name. Is there a transaction table for changes to cases? Or a way that we can roll back the transfers? Unfortunately, we do not have a back up. CRM does not offer a transaction logging sollution out of the box. The SQL database does, but this has to be configured and can only rollback all changes not specific ones. A thing you can try is (using the SDK) retrieve the last activity for each case ...

Recovering deleted items #3
Hi, I am encountering a strange problem. I am trying to recover mails which were deleted permanantly from my mailbox. After editing the registry settings i am able to recover all folders except deleted items. While I click on deleted items folder to recover the items outlook exits. Has anyone come across this problem? Thanks Shilpa hoe many items are in the deleted folder? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Ou...

different number format
Hi, this is probably an easy question but I am at a loss been an excel newbie. I have XP with my local regional settings, where the number format is 1.000,29 (onethousand and 29/100) on a specific excel sheet I need to revert these settings, in order to paste a large amount of data 1000.29 (onethousand and 29/100) anybody can suggest how to do this? TIA Hi Daniele, I think you shouldn't change your setting, paste the data and then use the code below to fix the numbers: Sub TextToNumberOnActiveColumn() Dim r As Integer Dim c As Range r = ActiveCell.EntireColumn.Range(&qu...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...

start and end times graphically
I have a query that has three fields ... emp, start and end. The start is the time the employee is schedule to work and end is the time the employee is scheduled to quit. I would like a report that shows graphically when employees are scheduled. For example: emp start end 100 7:00 9:00 101 9:00 11:00 102 8:00 10:00 emp times 7:00 8:00 9:00 10:00 11:00 100 ..................................... 101 ......................................... 102 ..................

CRM 3.0 installation: Reporting Services Error
Hi guys, I'm actually trying to install MS CRM 3.0 Prof. Edition. My configuration is: - Windows 2003 Server 32 bit set as domain controller (and up to date) - MS SQL 2005 standard Edition with Reporting Services 2005 (and up to date) - Exchange 2003 server (up to date) I want to install MS CRM on my server and get this error (this is not the exact version of the error message as I get it in french - i'm installing a french version): Failed publishing CRM reports Unknown error (0x80005000) Reporting Services seem to be well configures and there is no bad events in the event m...

Location of CRM data File #3
Could anyone please tell me the location of the CRM data file (not .pst) in Outlook 2007 Small Business. It would also be good to know if its' location can be moved. Thank you Ian ...

TIME....again
I've reviewed the posts and tips relating to time, but haven't found an EXACT answer to what I'm trying to do. Like one of the other posters, I keep an hourly logbook. I might enter a start time as 23:30 zulu, and an end time of 01:15 zulu. In this case, the time difference should equal 1:45, and then converted to tenths should be 1.8. When I'm not starting and ending on a different zulu day, I'm able to get the function to work perfectly. BUT HERE'S THE RUB. For DATE, I only wish to enter the local start date...regardless of what my zulu start or end date is....

Internet Time and Excel
Hello... Is there a way to execute the "Update Now" command that appears in the "Internet Time" tab of the Windows Time and Date Properties from VBA? Or maybe some suggestion to keep users of my program from tinkering with the time and date of the computer? Thanks in advance, Albert C. If you have the box checked in the Internet Time dialog box to automatically sychronize, it should revert to the Internet Time when logged on to the web. You could probably use the scheduling function to run a macro periodically that logs on to the web and allows the sy...