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

ADVERTISEMENT

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.

[URL]

View 4 Replies View Related

Convert Number To Date Format

Nov 14, 2011

I have following in cell A1

122211

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.

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.

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

Example......

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

19-Mar

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

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:

10:0:18:507
10:0:18:640
10:0:2:414
10:0:20:271
10:0:21:651
10:0:21:693
10:0:22:131
10:0:22:70

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

Convert Date To Different Format

Jan 29, 2013

I pull data from a database that outputs the date in this format, 1/29/2013 and I need to reformat it to input it into another database.

The other database requires the date to be in this format, 01292013.

What VBA code can I use to convert this date into the correct format?

View 1 Replies View Related

Convert To .xlsx Format

Dec 31, 2008

I have to download files from a database and save them in .xls format (the only format permitted to export from the database)

Given an open excel wb saved to my hard drive (or network drive) in non .xlsx format. (most likely .xls)

Switch the file to .xlsx format without loosing the open file. (re-open ok).

Caveats:
-Don't want two files
-Dnn't want to have the file name changed (except for the extension).
-Don't want to remain in compatibility mode.
-Don't want to wait a long time.
-Don't want to have possibility of file corruption.

I just want to take my non .xlsx file and then, turn into a .xlsx file. (if I save the file as .xlsx I am still in compatibility mode until I re-open the file. I also will have two files---very annoying).

View 9 Replies View Related

Convert A US Date Format In To UK

Sep 9, 2009

I am trying to convert US dates (with times e.g mm/dd/yy mm:hh) to UK date format I do not want to chage the time.

View 9 Replies View Related

Convert Dates To Another Format

Feb 15, 2010

I have attached a sample of the raw output of some call records from our telephone switch. My problem with the date data is twofold; first of all it outputs it in mm/dd/yyyy format, or so it appears. But when I try to change the format to mmm/ddd/yy Excel always interprets the 2nd pair of digits as the month instead of the first two. For example,the following 2 calls are from Jan 1st and Jan 2nd respectively;

01/01/2010
01/02/2010

but excel always treats the 2nd record as Feb 1st. I have read numerous posts from people with similar problems but I can't get any of their solutions to work with my data.

The second problem is that on the 13 of each month the phone switch suddenly stops putting a leading zero in front of the date making the data totally unreadable to Excel apparently. Again, I read a post of a similar problem but can't get their solution to work. My ultimate goal is to reformat the date data so that it is displayed as; ddd mm/dd and leave out the year entirely. Ex. Fri 02/12 as in today...Friday, Feb 12th. I really don't know if the problem is with the data or me at this point.

View 2 Replies View Related

Save Number As Text In Txt Format Wo Changing The Format

Mar 1, 2007

In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.

First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.

View 14 Replies View Related

Convert Number In TextBox To True Number

Nov 7, 2006

i have this function that poulates data from a userform into a sheet.

For lLoop = 1 To MAWBNoVar
With Range("B" & Rows.Count).End(xlUp)
.Offset(1, 0) = Controls("txtMAWB" & lLoop)
.Offset(1, 1) = Left(Controls("cbDestination" & lLoop), 3)
.Offset(1, 2) = Controls("txtGW" & lLoop)
.Offset(1, 3) = Controls("txtCBM" & lLoop)
.Offset(1, 4) = "= ROUND(E" & Y & "*167,0)"
.Offset(1, 5) = "=IF(D" & Y & ">F" & Y & ",D" & Y & ",F" & Y & ")"
.Offset(1, 6) = "=IF(F" & Y & ">D" & Y & ",G" & Y & "-D" & Y & ",G" & Y & "-F" & Y & ")"
Y = Y + 1
End With
Next lLoop

My problem is that the data being entered into the sheet for the lines highlighted in red is going in as text, and not true numerics, so that the subsequent formulae are not working correctly.

View 3 Replies View Related

Convert Decimal To 00:00:00 Time Format

Apr 9, 2014

How to be able to convert decimal hours into time format.

Here are the examples:

12.2 = 12:02:00
98.41 = 98:41:00
300.42.1 = 300:42:10

and so on and so forth...

View 11 Replies View Related

Convert Text To Time Format

Mar 5, 2014

If I had the below in a single cell what formula would I need for it to read just the 30mins in another cell in a time format?

17:30:00 + 00:30

View 12 Replies View Related

Convert From A Specific Date Format

Jan 7, 2009

If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View 4 Replies View Related

Convert Format To M Into Just Gives Me First Letter Of Month

Jan 20, 2009

How Do I Convert Date:

10/3/2008 into October
6/1/2008 into June
7/3/2008 into July

I have date 10/3/2008 in cell and if I convert format to M into Just gives me first letter of Month, so O, I want it to read October, etc.

View 2 Replies View Related

Convert Files To 2003 Format

Feb 24, 2009

I've been asked to change a massive batch of Excel 2007 files to 2003 format (to send to a client who doesn't have the newer version). Apart from going into these files (there's over 500 of 'em ), can anyone suggest a means of doing this? I know that Microsoft has a Migration Manager tool, but it appears this only converts the other way around.

View 2 Replies View Related

ADVERTISEMENT