I was wondering is there a formula to change three rows of numbers into a date?
Example:
COL.....A...B....C
ROW....1...1..1986
How do I change the 1 1 1986 to say 1/1/1986 as a date without having to physically type it?
why I cant change the date format for some of these rows?
Im trying to modify a excel file sent to me by a client but some of the fields seem to be messed up.
Could somebody explain what i must do to get all the rows in a format
yyyy-mm-dd
i have already tried to format the cells, but only some rows update!!
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.
EX: -1 needs to be 1
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
The format of the text in which I need to extract numbers is as follows:
23411268 - 23411270
Need to extract the following:
23411268
23411269
23411270
These numbers have to be listed in three seperate rows.
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies View RelatedI have the data as follows in each cell a number:
1 5 6 23 45 2
6 23 45 5 1 22
9 10 11 1 36 5
I have this kind of data going down about 2000 rows. I want to find how many times 1 and 5 appear in each row until there is no more data to read. I found the formula below but I cant use it for 2000 lines plus any extra that will appear in the future...
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have 3 columns and 200 rows. The first column is for Name of product. 2nd column for number and 3rd for $value of number of products. Have 200 rows. Have done a sort and checked. 2 sets of rows are for the same product name. I want to know if it is possible to add up values in 2nd and 3rd column, 2 rows at a time , then 4th and 5th column and so forth, by some formula rather than doing it manually?
View 4 Replies View RelatedI copied the number from A1 to paste in C1, as the graphic attached. But there are almost one thousand lines to correct. Any method more quickly?
View 7 Replies View RelatedI have a workbook that has 3 columns (G, H I) with numbers from 1 to 7 in each column. I want to tell excel that if a row has a particular 3 numbers in these cells then change it to a specified number. I want to do this for the entire workbook. Is this possible?
View 3 Replies View Relatedhow would i change this code to take in numbers instead of text?
View 14 Replies View RelatedHow do I change the first page number in Excel spreadsheet?
View 9 Replies View RelatedHow can i change "Dozen" into number.
DozenExpected result4.5534-54215.6186
If I input "wd" to cell d4 I want it to look up say E4 to E20 find the highest and add 100 and change the wd to "1234"
View 3 Replies View Related1Does Excel process worksheets (tabs) in the left-to-right order in which the tabs appear visually? Or in the order of the worksheets' index numbers as they appear in the VBE Project Explorer window? 2Is there a way to renumber or reassign index numbers to specific worksheets?
View 4 Replies View RelatedI have created a userform that upon clicking a commandbutton adds a line of 4 textboxes. Everytime the user clicks the commandbutton a new line of textboxes is created. The 4 textboxes in each line are described by a class (hope I am using the terminology correctly). And each instance is saved in a collection. The problem is that I am giving the user the ability to insert a new row in between two existing rows.
When this happens, I need all of the index numbers to re-order so that they are consecutive from the top of the form to the bottom. Example: The user enters 6 rows of data and then realizes that they missed an item that needs to be inserted between rows 3 and 4. Currently the rows are indexed 1 to 6 with 1 being the row at the very top of the form and 6 being the row at the very bottom of the form. If I insert a row in between 3 and 4 I will end up with the index numbers going from top of the form to bottom (1,2,3,7,4,5,6). Is it possible to reorder the index numbers so that the inserted rows index number will be 4 and each row after that will go up by one?
I have a column with the month as the full word (January or February, etc.).
I need to translate the words to numbers in a separate column (1 or 2, etc.)
I haven't been able to find a function that can help perform this task. "If"
statements will not go further than 8 levels. The month function will not
recognize it because it's not part of a full date. What other options do I
have? I feel like I'm missing something obvious here...
I imported a bunch of data that has some of the numbers listed as this 50-55.
what is the forumla to change it some both numbers are in different cells.
Pretend their are two Tabs in excel.
Tab 1:
"A1" contains a random number: "20"
Tab 2:
Column "A" has tonnes of random numbers:
6000 - Starting Balance (changeable)
5890
5800
5950
6080
6100
6180
6250
6300
an so on... (1,000 rows)
Based upon the number "20" that I placed in A1 on Tab 1, I want a formula that looks at the first 20 rows in column A within Tab 1, and then calculate the percentage change between the extreme "peak to trough" of the highest number to the lowest number within the 20 rows...
Below should make it really simple:
6000 - Starting Balance (freely changeable)
5890 -1.83%
5800 -3.33% - This was the lowest percentage change from the starting balance of 6,000
5950 -0.83%
6080 +1.33%
6100 +1.67%
6180 +3.00%
6250 +4.17%
6300 +5.00%
I have to change the layout of my numbers. At the moment the number have 3 decimal places but rounded to two decimal places and are separated with a dot (value in excel is 272.664 but shown just 272.66). After the macro or excel formula the output should be 000000000027266. So the last decimal places should be deleted (but the amount has still to be rounded!!!), the amount should be without a dot and the number has to be always 15 characters long (like in the example the above just with ceros before the number) I tried it in Format Cells but I couldn’t find anything. If I use the find and replace function to delete the dot, the third decimal place will be shown. Does anyone know if I can format it in excel or using a VBA code to change the number?
View 2 Replies View RelatedWould anyone have a script for something like this?
I'm looking at range c5:c1200
Starting at C5
If the first 2 digits in that number start with 01 then I want the formula to be in G5 = F5*12
if the first 2 digits are 03 then I want G5 = F5*24
I have several of these if statement to put in However I need it to check the entire range as well because the formula may be different in each cell (G)?
I enter 13 digit number in a cell, when I save my Worksheet in CSV format the number is changed.
Example:
I enter 9328627000014 (this is a barcode) After saving this file the number is displayed in the Formula Bar as 9328630000000 and in the Cell it show up as 9.33E+12 When I go to Format Cells, this cell is selected as Scientific. When I change it to Number, after saving it again it returns back to scientific with the old display. Also when I enter 2 digit number in another cell in another column and I choose to be displayed as number with 2 decimal places, after saving the file it returns back as General format. When I am saving this file as CSV a window pops up with a warning that my file may contain features that are not compatible with CSV. I can not choose text format in the cell because it is a datafeed and it has to be read correctly as number.
I use Microsoft Office 2003.
A text box on a Userform inputs numbers to a cell in a worksheet. I want the number to appear in the text box formatted #,##0.00 However, if I include the line
Code:
csDepositTextBox.Value = Format(csDepositTextBox, "#,##0.00")
in either the csDepositTextBox_Change or _AfterUpdate events, it causes the number to be stored as text in the worksheet. Curiously I can put the code in the corresponding event for another textbox and it does not corrupt the formatting.
I have a page for data entry that I want to automatically convert any data that is a percentage to a whole number (50% to 50 - not to 0.50). I am in the Sheet code (not the workbook) and I can't seem to get this to work. Data will likely be pasted by the user in bulk to a range (B3:H32) though data may not cover the entire range. I want the conversion to happen seamlessly to the user as my userbase will exceed 1,000 users, many of whom are not Excel savvy. When I enter data as a percent in the range on the sheet (using B column only at the moment for testing), nothing happens. Right now, the loop only covers the B column, but ideally, it will cover the entire range.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3:H32")) Is Nothing Then
For Each c In Range("B3:B32")
[Code] ....
I have data in range C1:C1000
The data is numbers 14-digits
When I try to convert the data to text the data appears like that
3.00052E+13
I want the data to appear like that
30005221800911
When I double click in the cell the problem solved but it will impossible to double click 1000 cells ...
I have numbers in A1:F1.
I have numbers in A2:F50.
How can I write a formaule that will check the numbers that is in A1:F1.
And if the number matches then change the colore of the number in A1:F50. too RED
This way you can just enter the new numbers in A1:F1 and if there is a match in A2:F50, you will be able to see it cause the number would turn RED.
I've got a wierd simple problem in Excel 2003. I have a laptop and a desktop machine, and I'm an accountant who uses the fixed decimal feature as a default, with it set to 2 decimal places.
On the desktop machine, if I input "23." into a cell and then hit enter, the value left in the cell is "23.00" However, on the laptop, when I do the same thing, I get "0.23" In essence, it ignores the fact that I entered a decimal point.
If I enter "23.0" in a sheet on the laptop, it puts "23.00" in the cell.
I thought maybe it was some sort of hardware thing, like the KB was messed up, but then I used remote desktop to try to see what would happen if I enter numbers into the other computer using the laptop, and into the laptop using the desktop. The laptop when connected to the desktop performed normally, just like the desktop machine. The desktop, when connected remotely to the laptop behved like the laptop machine. In other words, I believe this test takes Hardware out of the picture.
Which leaves some strange obsure setting in excel someplace tht is causing this behavior, and I can't seem to find it.
Does anyone here know why these two installs treat the data entry so differently? It's driving me nuts.
