Cell Format: Only Numbers (no Date) With Any Amount Of Decimals
I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).
I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".
I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Format: Using Numerals And Decimals
When I set my cell for Numbers it wants a decimal place. If I set it to 2 decimals then 100.1 (One hundred point one) would look like 100.10 (and print 100.10). If I use 100.10 (One hundred point ten) it looks and prints 100.10. If I set the decimal to one then the problem is reveresed. Setting to General looks like 100.10 (One hundred point ten) but when using VBa to match numbers it is matched to 100.1 not 100.10. Now I have it set to Text, but I see when I replace (find & replace) values like find 100 replace 99 the 100.10 becomes 99.1. Incedently I have a macro that compares (and copies cell values) numbers from one sheet to another and occasionally it skips a number, always a decimal value like 100.10 or 100.1. Or any thing x.10, x.1, x.3, x.19 any combination of x.x or x.xx. Is there a way to format so decimal numbers are decimal numbers and trailing zeros arent omitted? If the value is one decimal I want one decimal, if its two decimals I want two decimals.
View Replies!
View Related
Numbers Have Too Many Decimals
A spreadsheet created by exporting from QuickBooks as a .IIF file is opened in Excel 2003. A macro multiplies a cell value and returns 1.77999997138977 rather than 1.78. The 1.78 is required for importing back into Quickbooks. Part of the code is: ...
View Replies!
View Related
Converting Numbers Into Date Format...
I have a spreadsheet which has the date set out like this: 20070101 If there any way i can change this to either 2007/01/01 or 01/01/2007? I have tried using the date format (in the cells) but it just comes up with ######## i tried expanding the cell but no luck, this is very importand that i do this as its for a customer.
View Replies!
View Related
Recognising 1 Or Decimals As Numbers
I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals. Have I modified a setting?
View Replies!
View Related
Numbers Are Converting To Decimals
I received a complaint from one of my colleague that he is facing sudden problem with Ms-Excel(2002) The problem is When he feeds number 2 in to a cell it automatically converts in to 0.02. I tried using "Decrease decimal" and "format cells" option and even through uninstalling office and reinstalling a different version but the problem still continues.
View Replies!
View Related
Lookup Whole Numbers From Decimals
I 'm not sure why my custom function "minimize" is not working... I tried to do this with one of excels built in functions and would prefer a solution that way, but had to go the VBA route in the mean time. x and theta are paired together and I'm trying to reduce a select number of values x by their corresponding ratio.s It's corresponding ratio is determined by what degree value theta corresponds to. I wanted to do an if statement, but got confused... =if((B2>=$J2)*(B2<$J3)) , A2*K2 , if((B2>=$J3)*(B2<$J4) , A2*$K3 , if((B3>=$J4)*(B3<$J5) , A3*$K4, .... etc. etc. etc........................
View Replies!
View Related
Numbers Changing To Decimals
I have received an excel file from an external source. Every time I change a number in a cell it reverts to a 2 decimal number. eg. I type in 8710 and it converts the value to 87.10. I have looked at the number formats, cleared the numbers format but I keep getting the same result. I have also e-mailed the file to someone else and everything is ok on their computer. Is there a property in the excel program that I need to change?
View Replies!
View Related
Alter The Number Of Decimals In A Custom Format
is there any way by which you can alter the number of decimals in a custom format using just custom format process (ie no VBA). For ex: 12 should appear as 12 12.1 should appear as 12.1 12.26 should appear as 12.3 Note 12 should NOT appear as 12.0 So point being adding the "m" is an irrelevance at this time - the key is to vary the decimal points essentially based on MOD(value,1) = 0 If you can (I'm really thinking you can't) let's extend it such that 12.1 should appear as 12.1 but 12.26 as 12.26 I know you can use [value>x] type rules but I'm guessing you can't use formulae in those rules ?
View Replies!
View Related
Number Format (4 Decimals Places) In VBA
I am having an issue with number format in my VBA code. The entire column of column D is format as ###.0000 (4 decimals places). There are certains cells which require zero decimal places. I am using the following bit of code, but it is not working correctly. It is still formatting the cell as 4 decimal places.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Whole Numbers To Decimals & Calculate
I want to convert the number in a cell, G7, from a whole number into a decimal and then divide that decimal into a whole number in cell E7 and give me the quotient in cell E8. ie:312 = 6.0 286 = 5.5 260 = 5.0 234 = 4.5 208 = 4.0 182 = 3.5 156 = 3.0 130 = 2.5 104 = 2.0 78 = 1.5 52 = 1.0 Could this also be done with the entire range of numbers from 312 to 52 and giving decimals of 6.0, 5.9, 5.8 etc.
View Replies!
View Related
Prevent Automatic Date Format When Referencing Date Cell
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Format Cell To Remove Numbers
I m looking to format a cell to remove numbers that i don't need. For example, at work we can swipe a card and the card number comes up like so. ;11=00=0370904? is there a way to format the cell to remove everything but the 0370904
View Replies!
View Related
Format A Cell That Has Numbers And Text?
I am a biologist that works with transgenic animals and I am using Excel to keep records of my mice. I would like to have a column that includes the age as calculated by the DOB of my animal on any given day. Furthermore I would like for it to be smart enough to tell me in weeks for younger mice and months for older mice. If i do this, obviously i will need a txt string that says "x mnths" or "Y wks" or whatever. i have come up with an if/then string that works - but for some reason it will not allow me to format the number of decimal places if i include text. here is the function: =IF(((TODAY()-B6)/7)>12,((TODAY()-B6)/30.417) &" mnths", ((TODAY()-B6)/7) &" wks") the B6 cell is the DOB of that particular animal. so what this SHOULD shoot out is something to the effect of "4 mnths" or "3 wks" depending on the age of the animal. any animal older than 12 weeks will express in function of months and any younger will be in weeks. however what i end up seeing is something like this: 5.81911431107604 mnths I dont need any where near that many dec places...and obviously this doesn't fit in any reasonably sized cell......
View Replies!
View Related
Date Format To New Cell
Dates: ------ November 1, 2008 1 novembre 2008 In the formula bar, these formated dates appear as 11/1/2008. What I need to do is copy the formated dates and paste them into another column so that they show up as November 1, 2008 or 1 novembre 2008 and not as 11/1/2008. Does anyone know of a non-macro formula or function that can do this? Is there a "paste as is" function?
View Replies!
View Related
Format Cell For Date And Time
I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?
View Replies!
View Related
Cell Date Format Will Not Change
I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values. Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.
View Replies!
View Related
Format Date In Cell As Text
I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating. The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data. I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?
View Replies!
View Related
Counting Amount Incorrect Within A Date Range
I would like some help from someone on this subject if possible. I am running a stats report in a workbook where each member of staff have their own running totals of how much work they do and how much of that work is incorrect (per week). I have worked out that if I use the following:- =SUMPRODUCT(('Barry Brooks'!$A$1:$A$2000>=$B$95)*('Barry Brooks'!$A$1:$A$2000<=$C$95)) I get the total amount of work. To explain the above, it returns the amount of times a date appears between two dates i.e B95 is week commencing date and C95 is weekending; therefore giving the total amount of work in that period. What I am struggling with is that I need a formula to look at the above date range and then look at another column that has either a ‘Y’ or ‘N’ (for yes or no) and for the formula to count how many N’s there are (thus how many in that given range there were) It appears to me quite a small ask for Excel but I can’t seem to get it to work.
View Replies!
View Related
Automatically Format Date Cell As General
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 Replies!
View Related
Merge To 1 Cell Decimals Places
Below I have this formula in cell A6, in the cells the formula refers to B6 thru O6 contains formula's also. There is text and numbers in the cells. The cells with numbers are formatted as number to 4 decimal places. The problem is in cell A6 the numbers go to 15 decimal places. I don't know why but I need to keep it to 4 decimal places. =B6& "" &C6&G6&H6&I6&J6&K6&L6&M6&N6&O6 G01G41X0.0125Y0.0125D(offset#)F0.39 This how cell A6 is after the inputs are in below. G01G41X0.01249976025Y0.01249976025D(offset#)F0.386021052631579 This is how I need it to be below. G01G41X0.0125Y0.0125D(offset#)F0.3860 Can anyone figure this out. Wheather it is a formula or vb code
View Replies!
View Related
Pass Date In Cell To Variable & Format
I'll get straight to the point: where ( Date > 9/20/2007) The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?
View Replies!
View Related
Convert Text Format To Numbers Format In Vb
how i convert text format into numbers format in vb. Currently i have a formula in vB: Private Sub Textbox3_Change() Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value) End Sub however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up I faced an error "number stored as text"..how to i convert it to numbers format in vb.
View Replies!
View Related
Pass Date From TextBox To Cell & Change Format
One of my userform text boxes is for the input of a date. this date is being exported to a defined cell in excel, but in a US format, mm/dd/yyyy. i need this to be exported into excel in UK/Aussie format dd/mm/yyyy as our accounting software is getting confused (i am also). Private Sub TextBox3_Change() Range("C7").Value = TextBox3.Value End Sub
View Replies!
View Related
Collect Date & Output To Cell In Needed Format
I have created a standard input box for the date (UK) datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date) Sheet1. Range("e" & x).Value = datereceived I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
View Replies!
View Related
Renaming Workheet As Date Format (cell Reference) Using VBA
I download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu. Sub VisaMonthlyAcct() Dim shtName As String Dim newName As String ActiveSheet.Activate 'ActiveSheet.Name = shtName newName = "V" & Format(DateValue("A3"), "yymm") 'Old name for future reference = Visa0701 (2) ActiveSheet.Name = newName Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm) 'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm)) 'To select Date column & format Range("A3:A66").Selection.NumberFormat = "dd".............
View Replies!
View Related
Date Format: Extract Only Time And Paste It To Cell A56 In Same Sheet
I get one report.xls workbook by FTP. This book contain date and time in cell A14 in sheet1. e.g. 30-04-2006 19:38:00 I need to have macro to extract only time and paste it to cell A56 in same sheet. How can I do that. The date format is 30-04-2006 19:38:00 and it may be 05/05/2006 7:15 am. Or it could be any type of date format. Please send me how can I work with different date and time format to extract the only time and paste to other cell.
View Replies!
View Related
Userform Date Format Reverting Back To Us Format On Change Event
I have created a userform but I am having extensive problems with the date formats. My system is set to UK and short date is set to: DD/MM/YYYY When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format. So I finally figured out to use DateValue to format it correctly for example: ...
View Replies!
View Related
Copy Volatile Numbers To Persistent Cell Based On Date.
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33 The results I want will go in the G4:G33 column. C2 is Today() to avoid too many Today()s. Active!J10 is the value I need in the G column. Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances The formula I am using for the g range is : =IF(F24=C2,Active!J10) =IF(F25=C2,Active!J10) =IF(F26=C2,Active!J10) And so on down the column. I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
View Replies!
View Related
Pasted European Date Format Coverting To US Format
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another 18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
View Replies!
View Related
|
TRACKER
