for some reason every number in excel is automatically formatting as long date. while i can go into the individual cells, columns, rows, sheets and change the format it always auto formats to date at the outset.
View 7 RepliesI have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
System is generating IDs like 1-OCTO33, 1-NOVE44, which on exporting in excel converts to 1-OCT-33 & 1-NOV-44. How can this auto-formatting be restricted in excel?
To test, you can try entering in excel 1-OCTO33, it converts to 1-OCT-33.
I am looking for some setting in excel, which would prevent it on exporting to excel. Other options like changing column format to text or using a limiter ' will not work in this case.
I have to type in a long place name with 10 letters repeatedly and whereas before Excel completed it after I typed the first two letters it has now stopped doing so. Is there a way to reactivate this facility? Other names do get auto-completed when typed in the same column.
View 3 Replies View RelatedI am working on an inventory issue, we use a bar code scanner to read in inventory, and when we move inventory.
One of our inventory items has a number that is 20-numerical characters long, Excel seems to convert the last 5 characters to zeros (0).
An Example number would be: 89148000000286153971 Excel changes the number to: 89148000000286100000
I've got a string of text (that can vary in length) and contains a date (could be any month)..
I'm trying to extract the date from the string using VBA.
For example, the string may be:
"The following price of the service is effective until July 1, 2012 and may change whenever"
I need to first determine if the string has a date in it and then extract the date....
I did a search on mrexcel.com and found the following formula that will tell me if the text has a date (returns true/false)...however, i'm not sure how to use this in VBA nor how to find location of date....
=SUMPRODUCT(COUNTIF(B1,"*"&N73:N84&"*"))>0
B1 contains the text, N1:N12 contain the name of the months....
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39".
Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
I have cells of data with a long string of numbers such as: 20090507225836. Is there any easy way to convert them to date/time format, such as below? 5/7/2009 22:58:36
View 5 Replies View RelatedHow to assign a variable of type Long to the below code.
1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"
2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.
2) code failed because it seems like excel thought the search criteria is of same searchField.
How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedA little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies View RelatedHears a quick question..
Is there away in VB that a cell can be automatically set to time
What i mean at the moment when i need to type in a time i have to type it in as follows 10:00 ( the cell is formatted to time)
What i was wondering is there a way of programming in VB so that when the user types in 1645 it automatically changes it to 16:45
I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 Replies View RelatedI have created a couple of conditional formatting rules on the first row that fill the whole row of the table based on whether there a number in one cell or another. (Fill the whole row green if cell E3 has a number in it, or fill the whole row red if Cell F3 has a number in it).
I want to apply this to each row of the sheet but when I auto fill it down, the conditional formats fill but the reference cells stay as E3 and F3. This is causing the entire table to be filled instead of just the individual rows.
I don't want to have to put in conditional formatting rules manually for each row. Is there a way to auto fill down with the reference cells changing to suit each row like a normal formula would?
I have written in some conditional formatting to have a cells automatically updated based on a response in a cell above. That is: If a no response is chosen to a certain question, then all other questions in that range are to also be a No response. I have attached the document im working on with an outline of what the problem is!!!!
View 4 Replies View RelatedIn column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14
21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.
I use a query table to import data into a workbook which works fine
With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I have many autoshapes in my excel file and I want to use the "VBA version of conditional formatting" one two of these autoshapes. I am saying VBA version because conditional formatting does not work on autoshapes (just found that out). The autoshapes on which I want the conditional formatting to work are "AutoShape 73" and "AutoShape 74" (from Autoshapes > Stars & Banners > Explosion1).
This is how I want the conditional formatting to work:
If the text in the autoshape is "A", fill the autoshape with RED color and bold + white arial 12 font
If the text in the autoshape is "B", fill the autoshape with BLUE color and white arial 12 font
If the text in the autoshape is "C", fill the autoshape with GREEN color and bold + black arial 12 font
I need each row in the Range("A2:G" & iLastRow) to be formatted with the top and bottom border. I'd prefer to go loopless but if that's not possible I will use the loop.
View 2 Replies View RelatedI decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
I have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
Is it possible that when I type data into Cell A1, the date will automatically appear next to it in cell B1? I want this down the column.
the date has to be static. I tried this =if(isempty(a1),"",today()). This works but teh date changes everyday to reflect the current date.
Is there a way to have a date automatically appear in cell T8 when you first open the spreadsheet?
But every time after that you open it stays the same date as you first entered?
I would like to be able to input the Day and date in coloumn A and have the rest change accordingly
I wish to have it displayed as follows Monday 03/03/2014 followed by coloumn 2....Tuesday 04/03/2014.
There are 4 cells for each day set that have been "Merged and Centred". (B2,C2,D2,E2) display "Monday 03/03/2014" , F2 is a split , (G2,H2,I2,J2) display "Tuesday 04/03/2014" and so on.
I can make the dates change according to the first input but since i must also display the day as well.
I want a bit of code so that when I insert data into column 'C', it inserts today's date into column 'H' - in the same row. (So when I insert data into C22, the code inserts the date in H22). I want to do it with VBA code rather than an in cell function.
View 2 Replies View RelatedI have values in A2 cell & i need to take Average in to b2 cell as per date.
example A2 cell value is 1000 & today date is 12th then B2 value should =1000/12=83.33, like that tomorrow B2 value should 1000/13=73.92.
Is there a way to have a sheet automatically sort itself by date? I keep selecting cells and sorting manually but it get tedious doing it over and over every time I enter something new. Like if I had dates in row G every time I added something new it would automatically sort all rows by date.
View 9 Replies View Relatedhttp://www.fontstuff.com/vba/vbatut07.htm
The link above describe a VBA code for pop-up calender. I wondering is it possible to have the pop-up calender automatically display by just clicking on a cell in column A instead of pressing Ctrl C. The dates will be only in column A
I would like to make a list of vehicles with auto servicing dates appearing in the next column. How can I do this, example would be say 30/11/2008 was the last service, next column would be next service due 30/02/ 2009 and then a column next to that to say when service was done.
Pretty simple but I can't seem to get it to auto bring up the next service date which would be in 3 months time or 90 days.