convert a number to time

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Can't Edit Word Object in Shared Excel Workbook I have a shared Excel 2002 workbook on a network drive that is accessed by multiple people. Several of the worksheets in the workbook have embedded Word objects for entering text. I just found that the Word object cannot be edited while the Workbook is set to be shared. Does anyone know a way around this or an alternative to allow users to enter rich text into Excel? Thanks, Brian Tag: convert a number to time Tag: 53142
  - 2
    - Digitally Signed Macros I imbedded some macros in a worksheet and now when I open the worksheet it disables the macros (because they were unsigned) unless I lower the security setting which I would prefer not doing. So, per the directions I created a digital certificate. However, my macros still get disabled. Somehow, I assume I must then either apply the certificate to my existing macros, or recreate them under the auspices of that certificate. Unfortunately, I do not know how to do either...any help would be greatly appreciated! Thanks Tag: convert a number to time Tag: 53141
  - 3
    - Autofit columns and XML/HTML For our web based apps, we are starting to use Excel for all reportings. Using XML, I'm able to define what rows to repeat on each page, define page width, headers, footers, etc.. Works great for a cost efficient web reporting. The only issue I've come across is that I'd like to autofit the column width when the file is opened. Is there any way to issue a command to Excel within XML? TIA. Tag: convert a number to time Tag: 53140
  - 4
    - input date hi all, is this possible: i will type 011005 and then excel will automatically format it as 01/10/05 and will be treated as date? thanks. Tag: convert a number to time Tag: 53132
  - 5
    - Deleting Cell with formulas with Numbers Ex. =2+3 not = A+B Deleting Cell with formulas with Numbers Ex. =2+3 not = A+B I am trying to create a copy of workbook with only formulas, but some cells exist wit =2+3 which I do Not wanted. How can I delete all of this in a single click (Not Individualy) Tag: convert a number to time Tag: 53131
  - 6
    - Keeping formulas but not data's when copying workbook? I there a way to copy a workbook without copying any data. I would like to copy formulas without any data in the cells or notes for the cells being copied. I tried to put value Zero on all the cells but it's wipeout the formulas. Thank You Tag: convert a number to time Tag: 53129
  - 7
    - e-mail the current worksheet using a form button I want to create a submit button that when I click on it, the current worksheet/file will be e-mailed to someone. Instead of saving the file, then inserting into an e-mail. I have seen this before in a file before, but can't figure out exactly how it works. I happened to notice in the VB Editor I saw something like... CommandClick() Excel.Application.Send "myname@email.com" End Sub Or something similar to that. I don't know any VBA.. So if that's what I need to learn, then let me know. TIA Tag: convert a number to time Tag: 53120
  - 8
    - How to get reference to a range as string? I can get a reference to a cell as string with function CELL("address", A1), which will return the string $A$1. How do I get a reference to a range, for instance, A1:A5? Thanks. Tag: convert a number to time Tag: 53116
  - 9
    - One more final tweak... How do I further modify it so that if G17<H17 it also equals zero? Tag: convert a number to time Tag: 53114
  - 10
    - % of Schedule Calculation Hi all!! Unique problem here - at least I have not found the answer yet. I am certain one of you can help. I have the following 3 columns: Report Date Start Finish I would like to calculate % of schedule passed using these dates. In other words, I would like to calculate what percentage of time has passed for a particular activity on the 'Report Date'. PLEASE HELP!!!!!!!!! Mike Tag: convert a number to time Tag: 53099
  - 11
    - Adding space to left-justified Hi everyone: I am trying to accomplish the opposite (well, in a sense anyway) of the TRIM function, i.e. is there a way to add one or more spacea in front of a value in a cell where the content of the cell is left-justified? Thank you. Tag: convert a number to time Tag: 53097
  - 12
    - Compile Error Hi all, The first two lines if this I got from another response on here. I am trying to to step down through a list after each time I go through the copy and pastes and save the file. I get an error " Compile Error Invalid or unqualified Reference" and the For myRow = 1 To .Range("A65536").End(xlUp).Row is highlighted in yellow.I am starting to get really lost with this part. Any help would be appreciated. Thanks Dim myRow As Long For myRow = 1 To .Range("A65536").End(xlUp).Row Sheets("Contacts").Select Range("B7").Select Selection.Copy Sheets("Inventory").Select Range("D4").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False Range("C7").Select Selection.Copy Sheets("Inventory").Select Range("D5").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False Range("D7").Select Selection.Copy Sheets("Inventory").Select Range("D6").Select ActiveSheet.Paste Range("D7").Select Sheets("Contacts").Select Application.CutCopyMode = False Range("E7").Select Selection.Copy Sheets("Inventory").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("Contacts").Select Range("A7").Select Selection.Copy Sheets("Inventory").Select Range("X4").Select ActiveSheet.Paste Sheets("Contacts").Select Range("F7:G7").Select Application.CutCopyMode = False Selection.Copy Sheets("Inventory").Select Range("W6").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=3 Sheets("Inventory").Select Range("X4").Select Sheets("Inventory").Select Sheets("Inventory").Copy ActiveWorkbook.SaveAs _ Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value ActiveWorkbook.Close SaveChanges:=True Next myRow End With End Sub Tag: convert a number to time Tag: 53086
  - 13
    - flatten pivotTable hey i want to 'flatten the hierarchies' in a pivotTable and basically repeat the duplicated rows so right now i have US NORTH SOUTH WEST EAST CANADA WEST EAST I want to change this into US NORTH US SOUTH US WEST US EAST CANADA WEST CANADA EAST Isn't there any easy way to do that? I swear that it is an option somewhere; i just can't find it. In the programming world we call this 'flattening the hierarchial recordset' aka when you use ADO to consume OLAP data (vs using ADOMD to consume OLAP data) but since I'm consuming pivotTables from 4 different sources; it isn't really a choice for me to source the data directly (and i must import pivotTable numbers into a database) Tag: convert a number to time Tag: 53077
  - 14
    - Compute from a derived formula Hi I have cells in my XL with values like "2d 3h";since I cannot sum them up, I tried "SUBSTITUTE" the d with *8. For example, if the cell value is 2d it would look like 2*8. Now I would like to just use this value to compute, eventually to get 2*8=16. Any idea how I can achieve this? Thanks hari. Tag: convert a number to time Tag: 53074
  - 15
    - how to batch processing excel worksheet modifications? Hi, I hope that anyone can help me. I have 300 excel files to process, I don't what to have to open all these files one by one. What I have to do is the following: I have to modify the excel files in some simple way, add a column and insert the filename of the corresponding file in this column for every line items of my excel file. example : dir \name_my_excels\ has 2 files, fileA(5 lines) and fileB(10 lines) To do: open fileA, add a column, insert the filename in the columnF for the 5 active lines and close the file, open fileB, add a column, insert the filename in the columnF for the 10 active lines and close the file, and so on, this for 300 times. Is there a way that I can do this on one files and batch process my directory to have it done on all my excel files? A quick respond an a software, macro or anything like this would same me time and energy. Thanks a lot, Marc ps. Can anyone cross post this message for me, I'm new to groups ;) Tag: convert a number to time Tag: 53057
  - 16
    - using an NPV formula =NPV(After_tax_investment_rate,I14:I23) The problem I am having is that the I14:I23 needs to vary. In one instance it needs to be I14:I50 another time I14:I20. Is there a way to automate that so it automatically sees the different length on the list and enters it within those brackets? Help..... :-) Thanks in advance. Tom Tag: convert a number to time Tag: 53052
  - 17
    - Adding a month to a series of dates Hi I'm probably being stupid, but here's my dilemma! 8o) I have a date in cell A1, say 19/3/04 and I need to fill in the next 35 dates automatically - each being incremented by exactly one month: 19/4/04 19/5/04 19/6/04 ...etc I can't figure out an easy way to do this, such as adding a fixed number of days, as each of the month's have a different number of days. Any ideas? TIA Andy Tag: convert a number to time Tag: 53049
  - 18
    - Finding text that's in italics I've got a column in my excel data that contains text whereby some of the entries are in italics. I'd like to somehow be able to isoloate those items (via filtering or other means). I'd be willing to insert a column so that if the cell to the left is italics, a true response is indicated. Anyone know of any tricks? Thanks Tag: convert a number to time Tag: 53048
  - 19
    - Looking for files with UNC paths Hello, I need to scan all my file to look for those that contain UNC paths. Can anyone recommend a tool that will do this, and report on it? Cheers -- Saddam has continued to produce them, that he has existing and active military plans for the use of chemical and biological weapons, which could be activated within 45 minutes - Tony Blair on Saddam 24/9/02 "For a century and a half now, America and Japan have formed one of the great and enduring alliances of modern times." George W Bush -Tokyo, Japan, Feb. 18, 2002 suicide bombing is not a belief -CrazyAlec (internet spook) "I hear there's rumors on the Internets" - George W Bush -Oct. 9, 2004 Even today I am willing to volunteer to do the dirty work for Israel, to kill as many Arabs as necessary, to deport them, to expel and burn them, to have everyone hate us, to pull the rug from underneath the feet of the Diaspora Jews, so that they will be forced to run to us crying. Even if it means blowing up one or two synagogues here and there, I don't care. -- Ariel Sharon, interviewed by Amos Oz (published in the Israeli daily Davar, DEC 17, 1982 Tag: convert a number to time Tag: 53041
  - 20
    - Strange VBE Behavior Using XL2003 on Win XP Pro with all the latest patches etc, a rather strange thing is now occurring in the VBE. Each time I open a file in Excel, it appears in the VBAProject list (as per normal) and all works as expected ... no problems there. However, when I close the file in Excel, the listing in the VBAProject window doesn't update and still shows the file I just closed. Furthermore, even with no files open in Excel, I can still view the closed file's VBA project modules & code (I haven't tried userforms but I expect they would be the same). If I open my original file again, I then get two files with the same name showing in my VBE. The only way I've found to clear the VBAProject list is to close and re-open Excel. I tried Excel's detect & repair but that didn't make any difference. Any ideas? It's really annoying! TIA Jeff Robson Tag: convert a number to time Tag: 53039
  - 21
    - Creating a bigger list in Data Validation Hi all, Is Data Validation limited in terms of the items you can create for the drop down list. I am creating a shopping list and would like to enter in all my grocerie items, but there are too many. Any work around solutions? Tag: convert a number to time Tag: 53033
  - 22
    - if statements I'm trying to use an if statement and I'm not sure how to write it... here's what i want to accomplish: If cell H17 is blank or equal to 0 OR if G17-H17=0 then make cell I17 equal to zero, OTHERWISE multiply G17-H17 by 1.5 I'm sure a nested IF statement would get the job done, but I'm not sure how to do it... plz help. TIA M. Stanley Tag: convert a number to time Tag: 53027
  - 23
    - Custom Bottons appearing Every time I open my version of Excel 2000 a new button appears. The button has a lable of "Custom". Everytime I close Excel and open a new session a new button is added. So far I've let it get up to 10 buttons before I drag them off. The only way that I know how to get rid of them is to go to "Tools>Customize" and then drag each new botton down to the "Customize" pop up window. I've had Excel 2000 for several years now and this is only just beginning to happen. Any ideas? ----- Darrell dbdors@covad.net Tag: convert a number to time Tag: 53026
  - 24
    - template + page numbers I have a template with a cell reserved for the page number. I need to print the template 200 times with the page number incrementing for each page printed. In other words, the first page printed to read 1, second page 2, the third 3, etc. How can I get this cell to increment 1 for each page printed. Thank you in advance. Tag: convert a number to time Tag: 53021
  - 25
    - Sorting from right to left Is there any way to sort the right the format from right to left instead of left to right. 123456ABC 123456CBC 123456BBC I like to see that I can sort the last three characters. Thanks Rocky Tag: convert a number to time Tag: 53018
- Next
  - 1
    - Display numbers as rounded off I have a sheet full of dollar and cents amounts like $22.38 and 321.14. I want to display these amounts rounded off without editing each cell to remove the pennies and without going through and putting each amount in the ROUND function. I understand the ROUND function would need to be entered into each cell one at a time. I have Excel 97. In case it doesn't show, I am a computer dumb cluck. Tag: convert a number to time Tag: 53015
  - 2
    - Hyperlinks Hi there, I have Microsoft Excel 2003 and have a problem with hyperlinks. I have cells in which I have right clicked chosen hyperlink, named it news and then entered the hyperlink adress as www.news.com.au . When i click on the news hyperlink i receive an error message saying "Unable to open http://www.news.com.au/ . Cannot locate the internet Server or proxy server. I also have email addresses in the same spreadsheet and they open my email program no problems at all. I'm using Mozilla firefox as my web browser and Mozilla thunderbird as my email program which is why i point out that my emails open properly. I changed my default browser back to Internet Explorer but I got the same error message. Any help greatly appreciated. Tag: convert a number to time Tag: 52997
  - 3
    - Counting Uniques - multiple criteria Hi all. Got a long list of shipment records. Orders can have multiple line items of varying quantities and can ship on multiple days. I want to count how many unique orders were shipped on any given day for each customer. Order Line Customer Shipdate A 1 Ted 1/12/05 B 1 Mike 1/12/05 A 2 Ted 1/12/05 C 1 Mike 1/12/05 Total unique orders shipped to Ted on 1/12/05 = 1 Total unique orders shipped to Mike on 1/12/05 = 2 Any help... much thanks! Tag: convert a number to time Tag: 52996
  - 4
    - cannot find database I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks Tag: convert a number to time Tag: 52994
  - 5
    - Linking and Sorting worksheets I want to link a cell in Worksheet A to equal a cell in Worksheet B. Worksheet B has many columns and rows of data that I want to link to worksheet A. I can link the worksheets and have the correct values show in A. Problem: I need to be able to sort Worksheet B and maintain the same values in worksheet A. So if I sort Worksheet B, and for example Worksheet B cell A1 had the name Jake in it, but now Jake moved to A20,I want my link to follow Jake to A20. Can this be done. The formula moves when adding cells or deleting cells in Worksheet B but does not seem to move with the cell when sorting worksheet B. Is this a setting or am I out of luck????? Thanks Tag: convert a number to time Tag: 52992
  - 6
    - Moving through a list Hi all, Below is the macro that I have put together from , recording, some from help I got here and some of it I done. The sheet "Contacts" has aprox 300 rows of contact info that I need to copy and paste into the sheet called "Inventory" in the same workbook.I then save the "Inventory" sheet to a new workbook and save it with the name of the file comming from cell 'X4".I then save and close this new workbook.Now I need to go back to the original workbook " Contacts" sheet and move down to the next row and do it all over again until there are no more entries left in the " Contacts" sheet. I hope that is not too confusing. I have got a lot of help here in the last few days and I really appreciate it. Thanks Dim myRow As Long Sheets("Contacts").Select For myRow = 1 To .Range("A65536").End(xlUp).Row Range("B7").Select Selection.Copy Sheets("Inventory").Select Range("D4").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False Range("C7").Select Selection.Copy Sheets("Inventory").Select Range("D5").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False Range("D7").Select Selection.Copy Sheets("Inventory").Select Range("D6").Select ActiveSheet.Paste Range("D7").Select Sheets("Contacts").Select Application.CutCopyMode = False Range("E7").Select Selection.Copy Sheets("Inventory").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("Contacts").Select Range("A7").Select Selection.Copy Sheets("Inventory").Select Range("X4").Select ActiveSheet.Paste Sheets("Contacts").Select Range("F7:G7").Select Application.CutCopyMode = False Selection.Copy Sheets("Inventory").Select Range("W6").Select ActiveSheet.Paste Sheets("Contacts").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=3 Sheets("Inventory").Select Range("X4").Select Sheets("Inventory").Select Sheets("Inventory").Copy ActiveWorkbook.SaveAs _ Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value ActiveWorkbook.Close SaveChanges:=True Next myRow End Sub Tag: convert a number to time Tag: 52991
  - 7
    - Test Testing Tag: convert a number to time Tag: 52990
  - 8
    - Excel Template How do I get an Excel Template that I've created, to 'Save As' with an incremented number each time it is opened as an xls document such as Word .dot templates do? Whenever a Word Template is opened (new), it's opened as a document. When a user saves it using the "save as" command, it will prompt to save as "WordTemplate25" if the last template saved was WordTemplate24, for example. (All documents my users create are currently saved on the same network drive.) I would like it to do the same thing with an Excel Template, but when my users go to save the Template spreadsheet (which is an xls when they open it), they keep getting the same number at the end - 1. I hope I'm explaining this right. I am using Excel 2002 (where the templates are created). My users open the templates in Excel 2000. Could this be the problem? Tag: convert a number to time Tag: 52988
  - 9
    - Pivot-How to add field value to already made group If I have already grouped values in a Pivot is there a way I can add an additional value without ungrouping and then selecting everything plus the value I left out and then grouping again. I hate having to undo my group when I accidently left out a value. Thanks for any help with this. Tag: convert a number to time Tag: 52985
  - 10
    - Is there a TAB formula? I'm trying to setup a barcode scanner and I wanted to see if excel has a formula to TAB when inputting data. Tag: convert a number to time Tag: 52978
  - 11
    - Import data from Stored Procedure Hello. I am trying to import data using a third-party OLEDB provider and a Stored Procedure but cannot figure out how to do this. I get Microsoft Query open, type in the "CALL ..." command, it executes it successfully, but no data is returned. Can I import from a stored procedure? Thanks in advance, Mike Tag: convert a number to time Tag: 52977
  - 12
    - Compound interest calculation I think my bank has been underpaying me interest on a 5 year certificate of deposit. How can I calculate the interest I should be getting on, say, $2,500 for a five year term at 6.77% and show how much interest accrues at year one, year two, etc.? Ike Tag: convert a number to time Tag: 52966
  - 13
    - Little Green Triangles. In using Excel, a small green triangle has appeared at top right corner of some cells which contain a formula. The formula works, but am puzzled about this. The version of Excel is from Office 2003, can anyone explain this, I would be most grateful for some help please, or should I ask "Little Green Men ;-)) Seriously, I am asking this on behalf of a friend who does not subscribe to newsgroups. -- Derrick Fawsitt Tag: convert a number to time Tag: 52965
  - 14
    - autofilter if i have 4 worksheets in a workbook how can i autofilter all sheets at the same time. thanks mark Tag: convert a number to time Tag: 52961
  - 15
    - Chart labels do not print in 2003 Dave, thanks for your response. I copied one of the graphs that was affected to a new worksheet, and yes the label did print. So from that test i can assume it is not a problem with the printer drivers. Does anyone have ideas on this? ############################################### My spread has several sheets and two of these have eight graphs on each - all sized to fit on one A4 page per sheet. Since i have upgraded to Excel 2003 from 2000, some chart titles no longer print. I have upgraded my printer drivers and also tried this on other Excel 2003 machines, and they have the same problem. However, if i go back to an Excel 2000 machine it prints fine. The report is distributed to users of both versions. Any ideas how to fix this? Message 2 in thread From: Dave Peterson (ec35720@netscapeXSPAM.com) Subject: Re: Chart labels do not print in 2003 View this article only Newsgroups: microsoft.public.excel Date: 2004-12-10 13:57:59 PST I don't really have a guess. Have you tried it on different printers (if you have them available)? If you create a small test workbook with just one graph, does that work ok? Winshent wrote: > > My spread has several sheets and two of these have eight graphs on each > - all sized to fit on one A4 page per sheet. > > Since i have upgraded to Excel 2003 from 2000, some chart titles no > longer print. I have upgraded my printer drivers and also tried this on > other Excel 2003 machines, and they have the same problem. > > However, if i go back to an Excel 2000 machine it prints fine. > > The report is distributed to users of both versions. > Any ideas how to fix this? -- Dave Peterson Tag: convert a number to time Tag: 52959
  - 16
    - Find Control if i have 20 worksheets in a workbook, how can i use find to look through all sheets instead of going into each sheet to search.# thanks mark Tag: convert a number to time Tag: 52958
  - 17
    - auto formula I have a spreadsheet at work that will create a formula after I fill the cell needed to finish the calculation. I want to know how to do this for my own personal use. Someone please email me with a solution to how this is done. Thanks, Curtis Tag: convert a number to time Tag: 52951
  - 18
    - How I get CheckBox state on a Sheet. I want put a lot of checkBoxs, SpinButtons... on a Sheet. In VBA project, how I get these CheckBoxs state by its name? Thanks. Tag: convert a number to time Tag: 52950
  - 19
    - Page format in excel... Hello all, I'm working on a sheet that I have set up to print in landscape format. which is just what I want for the first 12 pages... but I need the next two pages to print in regular Portrait format. This is all in one sheet. Is there any way to do that? Thanks to all, Confused Man Tag: convert a number to time Tag: 52949
  - 20
    - Run Time error 9 Hi all, I thought I found the fault but I still can't get it to work! I am trying to copy sheet called "inventory" to a new workbook save it with the name of the value in cell X4 and close it and return to the original workbook. Thanks for any help Sheets("Inventory").Select Sheets("Inventory").Copy Sheets("Inventory").Name = "Inventory List" With ActiveWorkbook .SaveAs Filename:=Worksheets(" Inventory List ").Range("x4").Value End With Tag: convert a number to time Tag: 52937
  - 21
    - Copy and paste problem I am using Excel 2000. I have made a W2 form that I need to copy and paste for a total of 80 copies. ( I made the form just through using cell borders and resizing cells etc.) All of this is being done on the same worksheet. Through cell protection the user will tab from one cell to another filling out the appropriate information. THE PROBLEM. When I made this I had to decrease the height of some cells and increase the height of other cells. When I copy this down the sheet for a total of 80 copies the cells are set to the original default settings of 17 pixels in height. It copies the form I made but of course all of the cells all the way down are not the proper height. Is there a way to copy this and have it also copy the cell height? I sure hope so. I know everyone is wondering why I would go to the trouble when I could just use Quicken or some other type of program, but I just enjoy doing these things. Any help would be appreciated. John Kitchens Tag: convert a number to time Tag: 52936
  - 22
    - Currency and Numbers display Under the currency category of Format Cells, some machines only display four choices for negative numbers, while others display five. There is no choice for red negative with a leading minus sign. I've gone through to check if it's a version related issue, but can't seem to replicate the situation. We have Office 2K on W2K SP4. Any advice would be appreciated. Thanks Tag: convert a number to time Tag: 52935
  - 23
    - Run Time Error 9 Hi all, When I run my macro the code below is the last part. The last line before the "End with" is where it gives the " Run time error 9 Subscript out of range". Everything appears to work ok right until I try to save the file. Thanks for the help. Sheets("Inventory").Select Sheets("Inventory").Copy With ActiveWorkbook .SaveAs Filename:=.Worksheets("sheet1").Range("x4").Value End With Tag: convert a number to time Tag: 52932
  - 24
    - WHY, indeed! Thought I'd share this one. Someone somewhere in the company I work for designed a database solution for an asset-liability matching system. The design used separate tables of contract numbers for generating assets and liabilities. At year-end, one of my unit's contract numbers was in one table but not the other. Result: a > US$9 million screw-up on a single account. Maybe spreadsheet errors are more common than database errors, but database errors tend to cost more in the long run. Tag: convert a number to time Tag: 52930
  - 25
    - Automatically Updating Filter in Multiple Sheets I have data in multiple sheets. Each row of data in each sheet is identified with a text identifier. I can use auto-filter to select the prefered text identifier in each sheet so that it shows only that data required. This is slow and tedious. I have also tried the Advanced filter referencing a Named cell which indicates the indentifier. However, I still have to update each sheet manually. Is there a way to automatically update the single filter on all sheets simply by typing in the text identifier in a Named cell and pressing enter? Thanks in Advance, TonyG Tag: convert a number to time Tag: 52927

Hello everybody !!

Do you any idea for my problem ?
I've a column with times : HHMMSS
145204 == 2PM 52 min 04 sec
130298
000159
How to easily format this column as time format : 14:52:04 for example ?
Thank you for your help !!
rb

Re: convert a number to time by IC

IC
Fri Jan 14 10:12:59 CST 2005

Judging by your list below, the numbers are stored as text (otherwise the
last item would read 150).

You can't easily format the existing cells but you can extract the
information in time format to an adjacent cell.

Try this assuming your number is in cell A3:
=TIMEVALUE(LEFT(A3,2)&":"&MID(A3,3,2)&":"&RIGHT(A3,2))
Fpormat the cell as Time.

Ian
"rbus" <pleaseno@email.atall> wrote in message
news:41e7d9b1$0$12292$636a15ce@news.free.fr...
> Hello everybody !!
>
> Do you any idea for my problem ?
> I've a column with times : HHMMSS
> 145204 == 2PM 52 min 04 sec
> 130298
> 000159
> How to easily format this column as time format : 14:52:04 for example ?
> Thank you for your help !!
> rb
>

Re: convert a number to time by JE

JE
Fri Jan 14 10:15:37 CST 2005

One way:

If you just want to display the number as a time:

Format/Cells/Number/Custom 00\:00\:00

If you actually want to convert the number to a time:

B1: =--TEXT(A1,"00\:00\:00")

To convert the number into a time in-place will require a macro.

In article <41e7d9b1$0$12292$636a15ce@news.free.fr>,
"rbus" <pleaseno@email.atall> wrote:

> Hello everybody !!
>
> Do you any idea for my problem ?
> I've a column with times : HHMMSS
> 145204 == 2PM 52 min 04 sec
> 130298
> 000159
> How to easily format this column as time format : 14:52:04 for example ?
> Thank you for your help !!
> rb

Re: convert a number to time by rbus

rbus
Fri Jan 14 11:37:54 CST 2005

Hello all !
Thanks a lot it works !!!
Thanks bye

"rbus" <pleaseno@email.atall> a écrit dans le message de news:
41e7d9b1$0$12292$636a15ce@news.free.fr...
> Hello everybody !!
>
> Do you any idea for my problem ?
> I've a column with times : HHMMSS
> 145204 == 2PM 52 min 04 sec
> 130298
> 000159
> How to easily format this column as time format : 14:52:04 for example ?
> Thank you for your help !!
> rb
>