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.
I've been having a problem lately with Excel automatically converting all of the cells on my spreadsheet from standard number format to dates. I've created several spreadsheets for calculations and all the cells in the number formats that I wanted. It's been saved and actually reopened and used several times. I opened it this morning and now half of numbers are now converted to dates. I've had this happen several times on several different spreadsheets. Why is this happening? Is there a setting somewhere that I need to change?
I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:
Sub ForceToNumber() Dim wSheet As Worksheet For Each wSheet In Worksheets With wSheet . Range("IV65536") = vbNullString .Range("IV65536").Copy .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End With Next wSheet End Sub
Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.
I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.
im working on making a price list from a pdf file. however, the pdf file has the prices in euros listed as 1.300,00 and 765,00 and 45,00 and 1.565,00 . i was wondering if there was a tool or so in excel that can change the comma and periods in the number so they read as american format like 1,300.00 and 765.00 and 45.00 .and also if there is a formula or so to convert these numbers to Dollars in another column.
I have a program that exports the date as 20120621 (4 digit year, 2 digit month, 2 digit day). I have tried =datevalue(cell) but it returns #value!. I have tried =today()-(cell) and it also returns #value!. Is there a formula or something I can do to covert this field to a date?
I am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.
I need to convert these to numbers.
I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding £1000 but this does not seem to work.
I have this value in a cell 38761.8296804781 which is displaying as 38,762. I want to display it as $38,761,829. Is this possible without going through each cell and moving the decimal point?
We are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:
I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)
So 01=A, 02=B and so on...
I was trying to use if statements, but there are too many.
Do you know if you can use the cell formatting to change the sign of a number? For example, the cell contains 123.45, but I want it to show as -123.45. I don’t want to use a formula to multiply by -1, because the data is being extracted from a database and I have to do a fair amount of work on the report with the formulas to do that.
I can format the number and it shows a minus but will not use the minus if I then use it in a calcualtion. I could use a calcualtion with a *-1 in it but for ease of use by other users who may take part of the Excel report it would be better if I could format the number to negative.
I am using decimal and hexadecimal numbers. On a worksheet I can use all of the normal functions for this problem (Hex2Dec etc). I have loaded the Analysis toolpack, and also have an add-in loaded (but do not know how or when) that is called Analysis ToolPack VBA. In VBA I can use the fnction Hex to convert from decimal to hex, but there does not seem to be a reverseof this function. Am I missing something obvious or is this a 'hole' in the available functions? Is there a reference I ned to add in the VBE? if I try to use the worksheet function in VBA then I get a Value# error.
I have five columns on an Excel Spreadsheet with questions in each of the columns.
Below each question is a number value. In the rows below these number values the users will be answering the questions with Y or N. When the spreadsheet is complete I would like to replace the Y's with the number value shown under the question and replace the N's with a number value of zero (0).
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.
I have exported a dataset with references numbers and I want to convert those reference numbers into the titles they refer to. I have a separate sheet that matches the reference number to the title and would normally use a LOOKUP function [=LOOKUP(A2,Key::Table 1::A,Title)] to do this but, in this instance, I have multiple reference numbers in a cell separated by commas. How can I get the function to lookup multiple numbers from the same cell?
I have numbers from -50 to 50 going down column C. In column D, I want to enter a formula which will convert each number to a TRUE or FALSE. How do I do that?
I believe 1 = TRUE and everything else = FALSE, but I would like to know how to get that with a formula.
Has anyone created a marcro that will convert all the worksheets to Hard number, changing the formula results, (by Copying and paste, special, value). all at once?
I need a way to randomly generate a number( up to hundred trillion) in cell A1 and in cell B1 have the number appear in word form. Example: 129,114,023,131,453 will appear in A1 and in B1: one hundred twenty-nine trillion one hundred four-teen billion twenty-three million one hundred thirty-one thousands four hundred fifty-three The hyphens are important, commas between the numbers are not necessary. I know that for A1 I can use Randbetween() function to generate the numbers. The problem is generating the word form with the hyphens....
how to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.
Code: Function colNumToChar(colNum) As String Dim Num1, Num2, Num3 As Integer Dim Let1, Let2, Let3 As String
I want to convert numbers that were entered into a spreadsheet with commas in them. Since they were entered with commas of course excel reads them as text.
How can I convert them to numbers so I can use formulas.
