Type A Number In A Blank Cell With The General Format
Aug 10, 2009
My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.
E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.
How can I reset the General format to the original setting without this divide by 100 problem?
View 3 Replies
ADVERTISEMENT
Feb 18, 2013
I have a spreadsheet with a column full of dates
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
View 2 Replies
View Related
May 1, 2014
(3) examples when I type a number to a cell:
If I type a number "1", I want that cell to show 1.000% but not 100.000%.
If I type a number ".2", I want that cell to show 0.200% but not 20.000%.
If I type a number "25.5", I want that cell to show 0.255% but not 2550.000%.
I play around with the below custom format cells but they do not work.
_0.01*#.000%
_0.01*#,.000%
0.01*#,.000%
I could use two cells one for number, other cell has a % mark. But I rather to format a single cell if it is possible.
View 4 Replies
View Related
Aug 7, 2008
I'm trying to make an excel formula that generalizes product names based on their format. So the general format would be XYZ08/T13. I know a ? is equal to a general letter, but is their a symbo that specifically means letter or number. I'm running into problems using the ? because the formula ends up identifying additional items that are not products as products.
View 9 Replies
View Related
Jan 8, 2007
I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.
Is there anyway to automatically change the format back to general if a date is not entered.
I had this bit of code obviously it dosent work.
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True
View 4 Replies
View Related
Aug 22, 2013
Is it possible to change cell format from custom to general or text and keep the cell value
I have this in formula bar: 16/08/2013 4:37:00 AM
which is displayed in the cell as: 16/08/2013 4:37
I want to change the cell format to general but display the same information.
View 2 Replies
View Related
Oct 26, 2012
Every month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
View 2 Replies
View Related
Oct 17, 2012
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies
View Related
Mar 23, 2012
How we can convert any data which is in General format in to date format.For ex I've:
19/03/2012 14:57 (General format)
I want the output/result
19-Mar
View 5 Replies
View Related
Mar 12, 2009
I have a group of cells in E2:E4 that are 144.00 in, 240.00 in, and 72.00 in and are formatted as general.
I get these values when I export data from an AutoCAD program to Excel and I want to sum these values to get the total length.
I want to sum the three values and have the result be the same (i.e., ###.## in). The number of values can go to 30, so I need a simple line of code, =SUM(???)
View 9 Replies
View Related
Nov 9, 2008
How do I determine a cell's type (e.g., numeric or string or formula) and if numeric, the format (e.g., currency, general, scientific) including the number of decimal places.
Is there a function that returns the object's properties? Must I use isnumeric, isformula, and so on or is there one function that returns this information?
View 3 Replies
View Related
Jul 27, 2007
Does anyone have any clue of a VBA function that gets a cell and Changes the type of itīs contense?
I have been using the Cstr function as in the next example but I dont get the result I want
hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)
My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated
View 3 Replies
View Related
Feb 5, 2014
I need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
View 2 Replies
View Related
Jan 18, 2013
I have a cell which says November , 2012 in General format
In the next cell i want a date of the following month . Eg : 1-Dec-2012.
I tried =DATE(RIGHT(G4,4),LEFT(FIND(",",SUBSTITUTE(G4,",",",",1))),1)
View 1 Replies
View Related
Jul 28, 2007
I use software that exports date/time stamps in the format of: MM/DD/YYYY hh:mm:ss.s
I like to keep the data in this general format so that we can keep it consistent and it can be useful to others using other software.
I recently had to do some date/time stamp manipulations where I added 61 days to a dataset because the data was erroneously set to a diferent calendar. (Still don't know how that happened but it isn't relevent to this issue) When I set up a formula to add 61 days, it appears that it automatically converted it to the excel serial date/time format before adding the 61 days. Now I have a column of data that is mostly general format but has this portion in serial format. How can I get the serial format changed back to general so that the entire column is in the same format?
View 9 Replies
View Related
Oct 2, 2013
I am trying to achieve the below objective but getting error message
Objective: select last non blank row and offset to next cell type a message in it
error message: Runtime error 1004 : method 'Range' of Object '_Global' Failed
CODE:
Sub lastRowAll()
myvar = ActiveSheet.UsedRange.SpecialCells(11).Column
myrow = ActiveSheet.UsedRange.SpecialCells(11).Row
Range(myvar, myrow).Offset(0, 1).Value = "Experiments with VBA"
Range(myvar, myrow).Offset(0, 1).Activate
End Sub
View 6 Replies
View Related
Jan 25, 2011
Using Excel 2010 - I have a large workbook with multiple worksheets - been using it succesfully every day for a long time. All of a sudden every empty cell, and any cell not specifically formated in every worksheet has a default cell format of Time. Any new workbook created is OK.. defaults to 'General' - But, if I add a new blank worksheet to this particular workbook it defaults to 'Time' format. How did the default cell format for this workbook become set to 'Time' from 'General' and how do i fix it?
View 2 Replies
View Related
Jul 25, 2013
I have a procedure that allows me to view and make changes to data in a table. I list the current values for the item in one column and use simple formula to copy that value to another column where if there are changes that need to be made, the formula is simply overwritten. The Macro is then selected using a command button and the formulas are all overwritten using copy/paste values to keep from writing out the formula to the data table. These values are then all written back to the data table, current values are overwritten with whatever is in the update column, new data or old data.
I have one cell out of 48 that has decided to march to the tune of a different drummer. The format changes from General to Text and the formula written from the macro is what shows up in the cell instead of the value of the formula. Never a big disciplinarian, I have to wonder if I have been too lenient on the cell and this defiance is the price I have to pay.
The sheet is protected only allowing entry into the cells available for update.
Here is the bit of code that affects this cell (starting from a format of General:
Code:
Sheets("Product Data").Cells(ItemRow, 3).Value = Sheets("Update").Cells(6, 8)
Sheets("Update").Cells(6, 8).FormulaR1C1 = "=(RC[-6])"
I just don't see anything that would change the format, and these are the only two lines that even reference cells(6,8).
I tried to set the format for the cell from within the Macro, but with the sheet being protected, it just dumps me out to my error message.
View 1 Replies
View Related
Mar 13, 2013
Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.
I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)
View 2 Replies
View Related
Mar 11, 2008
The goal is to type a number in a cell (G4) on a worksheet, and then look it up in different work sheets (JANUARY THROUGH MARCH) in a different workbook,(NEW DAILY SUMMARY.xls) Once found, import the value of the cell next to were the number was found....
View 14 Replies
View Related
Nov 14, 2012
I have a vlookup formula: =VLOOKUP(TRIM(C101),Sheet2!$A$1:$B$500,(2), FALSE)
It is not working with General number values. Why is this? Is there something I should add?
The formula works if I swap columns to one with text or if I include text in the column.
View 7 Replies
View Related
Apr 22, 2006
I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price
View 2 Replies
View Related
May 23, 2009
I am in a role that has recently required me to do scheduling for employees every week. The schedule is 6000 rows of sites with columns A-N used for data such as site #, date, address, tech name. I want the tech name to be associated with the site number. Example:
Site Date Tech Name
413 5/17 Joe Smith
413 5/29 Blank
How do I get "Joe Smith" to magically be linked with site 413 and automatically populate in 5/29 when I enter it in initially for 5/17? I want all the techs to do the same sites from start to finish but the data entry is brutul. I am very basic in excel. Ultimately, I want to type the number 413 and have Joe Smith appear in the Tech cell. Is this possible?
View 9 Replies
View Related
Apr 29, 2007
I would like to create a formula that compares a number in one cell to the dates I have displayed in two different cells. If true, then I want it to display the Currency value from another cell.
______A________B________C________D_____
|1| Due Date | Amount | 1/01/2007 | 1/15/2007
|2|___4________$98_______________________
A2 is the DAY it's due
B2 is the AMT that is due
I can create an IF...THEN statement where if A2 is >= C1 BUT less than D1 then the value is B2, BUT it only works if I change A2 to an actual date which means i have to change it every month...which would not help with forecasting future transactions. So in the example above, the value would be in C2 and not D2.
View 9 Replies
View Related
May 12, 2010
I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.
Here is an example of what I need.
If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.
View 8 Replies
View Related
Feb 13, 2008
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.
Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.
I've almost gotten my routine finsihed but this is last remaining obstacle.
View 9 Replies
View Related
Oct 20, 2013
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies
View Related
Oct 31, 2008
As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)
If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.
If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.
It works, but doesn't work.
If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!
I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.
View 4 Replies
View Related
Jan 9, 2008
I know that I need to use Conditional Formatting, but I can't get the formula code correct.
This is what I need it to do:
If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill.
If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal
If cell A2 is blank then leave formatting as per normal
Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </>
I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
View 5 Replies
View Related
Sep 26, 2009
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
View 2 Replies
View Related
