I have built a spreadsheet that pulls data into B60:AA240 (Sheet name is "Actual Numbers Report") from a different sheet in the same workbook. Some of the data is in Number format and the other is in Percent Format. What I would like to do is if AL10 in the Actual Numbers Report sheet says "Actual Numbers" then I would like the cells in B60:AA240 convert to a number format "000,000,000" If AL10 says "Trends" then I want it to convert the cells in B60:AA240 to a percent format "0.0%". I tried creating some code, but it doesn't seem to work.
Private Sub Convert_Percent()
If Not Intersect(Target, Range("B60:AA240")) Is Nothing Then
If .Range("AL9") = "Actual Numbers" Then
Range("B60:AA240").Select
Selection.NumberFormat = "000,000,000"
ElseIf .Range("AL9") = "Trends" Then
Range("B60:AA240").Select
Selection.NumberFormat = "0.0%"
End If
End If
End Sub
If this can work then the 2nd question I would have is can this same line of thinking work to format the chart that this data is pulled from? So if it is Actual Numbers the chart would be in a number format and if it is Trends then it will change to a percent format?
In the attached sample (with macros enabled), you will find the problem when pressing the button “INDTAST DATA” (I apologize for the linguistic challenge, but the XL-sheets are in Danish… To relief – check the crash course in Danish below) and then entering some number in the two last textboxes (called “Forventet ændring i antal timer I næste kvartal (%)” and “Forventet ændring i omsætning i næste kvartal (%)”)… If you enter something there, the result will be multiplied by 100 in the worksheet.
I would like to be able to simply enter a full number – like 12 or 9,5– which will then be entered into the worksheet as 12% or 9,5% (and not 1200% or 950%)… I think the answer lies in inserting some code in the VBA code, when the macro writes the data to the worksheet, but you guys know more about it than I do...
I can, of course, enter a full number in the textboxes – followed by a %-sign, but that will slow down the process significantly as well as increase the risk of errors…
Virksomhed = Company Kvartal = Quarter År = Year Branche = Industry Fakturerede timer = Billed hours Faktureret omsætning = Billed revenue Timeforventning = Expected hours (next quarter) Omsætningsforventning = Expected revenue (next quarter) Indtast data = Enter data
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
I just copied a range of data from a website however these numbers are in TEXT format.
Basically each number in this data set has a SPACE behind. This turn the number into a text itself and i cannot do a sum for this range of data. I tried TRIM function and also tried to format it to number but no luck. Also tried to mutilply the range to 1 yet they're still in text format.
There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:
"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.
The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.
Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12
I have a percent 3.14%, that I want to show 15, 20 and 25% above and below that percent. I am showing below the results and then formula. The top calculation results in the same on both the left and right. However the -15,-20, -25% results are different. Which is correct.
3.14%
20% +/- 3.14%
0.63% or 6.28%
1.57%
2.51%
[code].....
basic math, but I don't understand why the results are different.
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
In D2 I have: =B2 In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?
I have a spreadsheet that in various cells have numbers that have been been formated as text and have a leading zero and therefore displays the green flag in the corner of the cell. I want to change them to number format and clear the green flag.
using something like Cells.ClearFormats Cells.NumberFormat = "0.00"
doesn't seem to work. whist it shows the format as number it still leaves the green flag and the leading zero.
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.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
I have imported date field values from ClearQuest(IBM Product) into Excel worksheet. after import the data display format in the excel looks like
Thursday, February 19, 2009 9:11:42 AM
Now I need to calculate the age of the defect in number of days by using the formula
=NOW()-J26 where the cell J26 contains the above data . However I always get the display as #VALUE in the cell.
I changed the field format for cell J26 to custom dddd, mmmm dd, yyyy h:mm:ss AM/PM but still does not work.
My question now is- how do I convert dddd, mmmm dd, yyyy h:mm:ss AM/PM to mm/dd/yyyy h:mm:ss AM/PM so that I would be able to find the age of the defects?
Or is the a way to change the format for NOW() to something dddd, mmmm dd, yyyy h:mm:ss AM/PM?
I am having some issues converting the dates in column A on the attached spreadsheet to the mmmm format. I cannot get the dates to change format at all so I was hoping someone might have some ideas why. I have tried everything I can think of. The only thing that has worked so far has been to retype the date in the cells. I have 32000 rows of data I have to convert.
here the decimal separator is " ,". I have some 8000 data values with decimal separator ".". This is the format of the exsiting cell " +1.0000000000000000E+00 ". I need to have this converted to "1,00". I would like to have a macro which u cud do this perform!
See the attached file. In the RawData tab, I have the Type, The Date, and total for that date and type. Using a macro, i would like to redesign the output(onto another sheet) so it can be updated to a database table(i already have the code to export to database). The output should look like what is in the "OutputExample" tab. Figures in the OutputExample tab are only for the first three dates. So currently, I have the output as: