I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
I need to convert the following string into Excel dates:
Jan 1, 2009 Jan 12, 2009 Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.
I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.
I'm currently trying to copy a dynamic range based on the row number of a selected listbox item. I'm able to get the row number of the selected listbox item from the data tab in my file. I, however, am stuck when it comes to copying a fixed column / varying row range from this tab and copying it over to another tab.
Private Sub CommandButton2_Click() Dim ID As Long Dim Row As Long Dim Cnt As Long Dim SearchTermsStr As String Dim SearchTermsRng As Range
[Code] ....
SearchTermsRng is returing "Nothing" but the SearchTermsStr returns "F3:O3"
I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.
The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.
I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.
I am trying to check my sheet names to see if they convert to a valid date format. If it doesn't, then I want it to check for the next criteria (ex - if it equals "Page2"). With the code below I keep getting an error of type mismatch. Code is below.
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I have a spreadsheet that has 3 different dates in 3 different cells:
B4 - 3/24/2014 B5 - 4/6/2014 B6 - 4/14/2014
From that information I need once cell to display "Mar 24 - Apr 6 paid Apr 14"
Reason being is that I am importing journal entries into an accounting program and I am allowed one cell each row for the description. This would let me change the dates only and my descriptions will fill out before import. It is important that the months are abbreviated to 3 letters as I am limited on how many characters the description can be.
I have a function that will be used across 10K+ records at a time, so I was taking a look at optimizing it a bit. At the start of the routing I have a string with a 9 to 15 digit numeric value in it. I wanted to load each individual digit into a byte array. I started out using:
byVal = StrConv(sVal,vbFromUnicode) But that actually loads the ASCII Character codes into the array not the digits themselves. From there I considered looping through the array applying CByte(Chr$(byVal(i))) but then I wondered if it would make more sense to just load the array with Mid$(sVal,i,1)... Does anyone have tested knowledge on which performs better? Is there an option C I have missed?
I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.
1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.
2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.
3 the data that is in :ss, ie :44, is recognized as a string.
Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.
I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.
I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.
How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example
Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value
Backstory: I am converting PDF documents to Excel files and some of the numbers are being transformed into a wonky date format which to the ***** eye looks semi correct but the underlying value in the formula bar is incorrect. Basically I need to copy and paste the value but when I paste special values it doesnt work. Below is the example
The cell value should be: 9856 When it is converted to excel the value shows 98 5 6 but in the formula bar it shows: 5/6/2098 The cell is formated as: Custom yy m d;@
If I get the value in the cell to read 98 5 6 I can use trim or something to get it down to 9856 but the issue is when I use those functions they return 72446. I guessing this is because they read the formula value not the cell value.
I need to somehow get the value in the formula bar to read 9856.
I have dates being imported (AS TEXT) to excel in various formats and want them all to appear in their cells in the same 8 digit format (MMDDYYYY) so it can be exported to another program
for example:
Single Digit Month and Day cell A1: 3179 (convert to 03011979) cell A2: 30179 (convert to 03011979) cell A3: 030179 (convert to 03011979) cell A4: 311979 (convert to 03011979) cell A5: 3011979 (convert to 03011979) cell A6: 03011979 (already correct)
Single Digit Month and Double Digit Day cell A7: 52165 (convert to 05211965) cell A8: 052165 (convert to 05211965) cell A9: 5211965 (convert to 05211965) cell A10: 05211965 (already correct)
Double Digit Month and Single Digit Day cell A11: 12523 (convert to 12051923) cell A12: 120523 (convert to 12051923) cell A13: 1251923 (convert to 12051923) cell A14: 12051923 (already correct)
Double Digit Month and Double Digit Day cell A15: 112180 (convert to 11211980) cell A16: 11211980 (already correct)
I'm a moderate user of excel with some experience but need help creating a specific if statment.
I have a column "A" that shows dates formated like this: 060115 (January 15, 2006). Because excel does not recognize this as a valid numerical date I need column B to show what year the date falls under. To complicate it I need it to reflect a seasonal year from April 1 YYYY, to March 31, YYYY.
So here's an example. Say I have a date in column "A" of 050612 (June 12 2005). I need column "B" to look at that date and determine if it's >040105 and <033106 and return a result of "2005". I then need the formula to calculate an answer for up to 5 years worth of returns.
My question is about converting a Literal String to Character Code. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
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.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I recieve a spreadsheet each month with the following column of data:
9 Paddington House PO Box 73 Whiteleys of Bayswater 9-10 Grove Road 134/138 Drymen Road 44-46 The High Street 1 Midland Road 48 Donegall Place
As you can see, it consists of addresses, each line is a separate address. I need it to be converted so that the data is split into the following 5 fields:
Street number from street number letter from street number to street number letter to street
If there is no street number , then it should just enter the data into the street field. If there is a range such as 44-46, it should enter the 2 numbers into the from and to fields. Sometimes this is in the format 44/46. Also, sometimes there are letters, eg 44a-44b, in which case each section (ie 44, a, 44, b) needs to be in a spearate field.
