Convert Number To Percent Format

Dec 18, 2008

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
Selection.NumberFormat = "000,000,000"
ElseIf .Range("AL9") = "Trends" Then
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?

View 9 Replies


VBA Userform – Convert Number To Percent

Feb 12, 2010

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

View 4 Replies View Related

Convert Number Format To Time Format

Nov 28, 2013

How do I convert 11.20.00 in A1 to 11:20:00 in B1?

I've tried =TEXT(A1,"hh:mm:ss") to no avail.

View 3 Replies View Related

Convert Number Format To Text Format?

Oct 11, 2012

I want to convert number format to text format, any formula ?

View 6 Replies View Related

Excel 2010 :: Convert Text To Number And Format Number Without 2 Decimal Places?

Oct 23, 2011

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.


View 4 Replies View Related

Convert Number To Date Format

Nov 14, 2011

I have following in cell A1


This represents 22-Dec -2011. Formula to convert the number in to a date format.

View 3 Replies View Related

How To Convert TEXT Into NUMBER Format

May 5, 2007

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.

View 9 Replies View Related

Convert Number To Date Time Format?

Dec 9, 2013

how to convert number to data time format:

eg. I have number 20121231230000 and I need its conversion to 31/12/2012 23:00.

I have tried to use fucntion date with left, mid, right but still I do not know how to extract time.

View 2 Replies View Related

Convert Serial Number To Date Format

Nov 27, 2007

Quick question:

If I have a serial date, e.g., 37883, in Excel, how can I convert to 092007, using that exact format?

<snip by admin. Read the rules!>

View 4 Replies View Related

Cannot Convert Numbers Stored As Text To Number Format

Dec 14, 2009

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.

View 9 Replies View Related

Automatically Convert 4 Digit Number To Time Format

Aug 30, 2006

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

View 9 Replies View Related

Taking Percent And Adding Subtracting Percent Formula

Sep 12, 2012

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.


20% +/-





basic math, but I don't understand why the results are different.

View 3 Replies View Related

Adding A Colon ( : ) To Convert A Standard Number Into A Time Format

May 6, 2009

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

View 3 Replies View Related

Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format

Jun 7, 2006

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.

View 7 Replies View Related

Macro Or VBA - Search General Format Convert To Date Format

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

Convert Data In Invalid Format Into Correct Format

May 1, 2014

I have around 30k data. which is in invalid format.

Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921

I want to convert this into correct format as below with start letter from

MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921

I want to know which are formulas I have to use to get this info in correct format.

View 1 Replies View Related

Convert A Date Format To A Text Format

Oct 6, 2008


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!

View 3 Replies View Related

Convert General Format Into Date Format?

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


View 5 Replies View Related

Convert Text Format To Numbers Format In Vb

Nov 21, 2006

how i convert text format into numbers format in vb.

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" ooes not sum up

I faced an error "number stored as text" to i convert it to numbers format in vb.

View 9 Replies View Related

Convert Date Format To Another Format

Sep 11, 2007

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?

View 9 Replies View Related

VBA Convert Format

Oct 23, 2009

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.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.

View 9 Replies View Related

Get/Convert Number From Clipboard As/To True Number, Not Text

May 29, 2008

Does clipboard method gettext retreive the text from clipboard only, not number? What if numbers are copied (Ctrl C) to clipboard?

View 9 Replies View Related

Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

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.

View 5 Replies View Related

Excel 2010 :: Number Format Keeps Switching To Date Format?

Aug 1, 2013

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.

View 3 Replies View Related

How To Convert A Date Format

Jan 23, 2014

I have a column with dates like:

Jan 22 2014
Dec 7 2013
Mar 17 2013

which I want to convert to a more standard (sortable) format.

Maybe 1/22/14

View 4 Replies View Related

Convert Date Format

Mar 5, 2009

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?

View 3 Replies View Related

Convert The Date Format

Jul 22, 2009

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.

View 6 Replies View Related

Time Format / How To Convert 10:9:8.13 To 10:09:08.013

Nov 29, 2013

I'm trying to sort a dataset that contains time stamps: each line is an event with a timestamp containing milliseconds and no leading zeroes:

if I sort, I get this result:


which is wrong on so many levels...

how can I correct this?

I wanted to convert to milliseconds but how to extract text relative to the delimiter ":"

I tried custom format, but Excel won't accept the formats I proposed "hh:mm:ss:000", "h:m:s:000", "00:00:00:000" it's all for the cat.

View 11 Replies View Related

Convert Cell Format

Jan 29, 2009

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!

View 2 Replies View Related

Convert Data To Different Format

May 5, 2009

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:

View 4 Replies View Related

Copyrights 2005-15, All rights reserved