I am exporting data to Excel from a 3rd party application - the bulk of which are date entries. For some reason they do not come across as date fields in the cells, and you need to double-click on each cell to correct it. For instance if I try to filter the dates straight after the export to Excel, the cell entries are not recognised.
Anyway, I managed to get around this by running a macro on each column, e.g. for column C:
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
I copied some columns of dates form a website into an excel workbook. Unfortunately excel does not recognise the text and therefore I can't sort the columns from oldest to newest.
I have tried the operations here [URL] as I though spaces might be the problem but with no success.
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
I have imported data from textfile reports generated from a dBase system with the following Sub ImportData() ......... End Sub
The problem I have now is that during the operations on the date collumns (G-K) Excel has converted the date to dd/mm/yyyy format, just as I wanted - but has for instance sometimes interpreted the date 10/06/2005 (10 Jun 2005) correctly and sometimes as 06/10/2005 - with no consistent logic I can discern.
I can't get hold again of the source data (the reports) and would need to somehow correct the dates.
The data is historical booking information of a Tour-operator. Therefore there are some hints that would allow me to check if a date makes sense or not. In each row is a start-and an end date in collumns D & E, which would need to be checked for correctness. The following may giv you an idea what I am babbling about: .....
I have a worksheet used for scheduling. When a members time is updated, it updates the counting cell for that time by subtracting 1 (thats simplified, the forumla is more complex than that).
I have five teams and five workbooks for each team to do it's own scheduling. In just ONE workbook, the Worksheet_Change() event has stopped executing. It's fine in the others. I renamed the workbook to archive it then put another workbook in it's place and now that one works just fine.
The workbook that I've archived, I hate doing that not knowing what would cause the Worksheet_Change() to stop being recognized. There is no code on the sheet or related to the sheet that would stop it or cause events to be cancelled.
I wanted to know if there is some secret keystroke combination that may have been inadvertently clicked that would cause events to firing or stop being recognized?
I have created an Excell Add-In file (.xla). I just can't seem to get the procedures to be recognized and/or run. I have even created a custom menu based on the following tip: http://www.j-walk.com/ss/excel/tips/tip53.htm
When loading Excel will add the menu to my main tool bar. What is frustrating is that when trying to run one of the subroutines from the new menu item, I constantly get "The macro 'spreadsheetname.xla!macroname' macro cannot not be found'
I've checked the security settings and have verified that all add-ins are to be 'trusted' and I've lowered macro security to low. All of the subroutines have been declared as public as well. My suspicions are that there is some setting somewhere that is causing issues, but I just can't seem to think of what it is.
I have a system that exports start and finish times into a single cell as text (eg 9:30 AM - 7:00 PM)
I am trying to do a simple count of how many shifts start before 7:00 AM, or finish after 6:00 PM, and am using the text function to extract the individual time into another cell.
My problem is that i cannot get this cell to be recognised as a time instead of text? I do a simple "is this cell less than another cell" but it tells me no, even though the time is 5:00 AM.
I defined two custom buttons in Workbook_Open() function:
Private Sub Workbook_Open() Dim cb As CommandBar Dim ctrlInit As CommandBarControl Dim ctrlComp As CommandBarControl On Error Resume Next Application.CommandBars("Project").Delete On Error Goto 0 Set cb = Application.CommandBars.Add( Name:="Project", temporary:=True) With cb .Visible = True Set ctrlInit = .Controls.Add(Type:=msoControlButton, temporary:=True) With ctrlInit .FaceId = 68 .Style = msoButtonIcon .Caption = "Retrieve Template Types"............
In Module1 I must to turn ".enable" properties to Turn, but Excel don't recognize these variables there.
I have a .csv file with measurements. I can open it in excel, and have succeeded in make 3 separate columns from it and save as an .xlsx file. But the problem is I still can't do calculations with the data. it are all values from a form like "-3.44e-07" but excel recognizes this as text. I don't have a green triangle in the corner and multiplying by 1 doesn't work.
Apparently i can use them as numeric values in MATLAB, but I don't know how to do this in excel. And I have 9 files like this, each containing 3 columns and 600 rows, so changing all the cells one by one is no option.
I use the dutch language version of excel so I hope I translate the specific names of things in excel correctly.
I created in module custom toolbar with a button, see the
Option Explicit Public ctrlGUI As CommandBarControl Sub CreateToolbar() Dim cb As CommandBar On Error Resume Next Application.CommandBars("Cust").Delete On Error Goto 0 Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True) With cb .Visible = True Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True) With ctrlGUI .FaceId = 3205 .Style = msoButtonIcon .............
The following simple formula was written in Excel version 97. when I try to use it on a newer version (03) it does not seem to recognise it as a formula? =IF(F66="yes",1,IF(F66="N/A",1,0))
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
I have a code that runs when I open the workbook. It asks if this is a new quote, if it is it should then open userform1. The problem is that sometimes when I click yes I get a "Path/File access error" popup, and then a VBA error box with "Run-time error '75' Could not find the specified object".
When i debug, it highlights the line "userform1.show"
IF i end, and then run it from within VBA it will then run fine.
Sometimes when I open the workbook it will just crash excel, and when I reopen it, it runs fine.
Simple code -
Sub Workbook_Open()
answer = MsgBox("Is this a new quote?", vbYesNo) If answer = vbNo Then Else userform1.show
i have a number (%) in the cell B3, which excel doesn't recognizes as a number. Is there anything i can do "excel-wise", instead of manually rewriting the number in the cell, for excel to recognize it as a number. Changing it to a "number" or "percentage" in the cell format doesn't work.
I have two text boxes on my sheet that I am using as a log in sheet. I am not sure what triggers them but they stop allowing me to type in them to log in and instead are recognized by excel as pictures and ask me if I want to format them. I have fixed them in the past by deleting them and adding new Active X text boxes to the sheet but then after a while they turn back into pictures.
By the way the text boxes have been named T1 and T2 and are used in VBA behind the scenes to determine if the details that are typed into them match existing accounts.
I'm trying to query Access for some data. One of the fields I need to grab is called MOD, which also happens to be a VBA function. How do I get Excel to recognize MOD as a field name instead of a VBA function? (Renaming it is not an option)
Code: stSQL1 = "SELECT MOD FROM CST_ClaimbyLop WHERE CoCName='" & myCST & "'"
I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
I have a column of figures in a file sent to me recently which are dates but for some reason have been reversed eg 20140321. This is 21st march 2014. However I have tried formatting the number as a date (as we would normally see it in UK eg 21/03/14 or similar) and I cannot get Excel to recognise it as a date - I just get a long row of asterisks. How do I get a recognisable date sequence?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit 413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012 18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them. note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
