I want to show a date like 18-12-2008. This wil normal work with =()TODAY-356
But when I output text with this formula it will only show numbers:
="date "&TODAY()-365&""
I want to subtract 1 year of today.
I tried to set my cell to the date format but this did not work.
I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
View 2 Replies View RelatedI have following numbers in Column A. I need formulas to get results shown in Column B and C.
ColumnA ColumnB ColumnC
Numbers Formula?? Formula??
200001 January Jan 00
200101 January Jan 01
200102 February Feb 01
200710 October Oct 07
200912 December Dec 09
dates column displays numbers even though the format in the format cell options is date format.
the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.
EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
I am using a worksheet change event to display the date and time that certain cells are manually changed. However, I would also like this to happen when a cell displaying a value based on a formula changes.
View 1 Replies View RelatedI have a excel file which deals with accounting systems. It has the Loan date in Cell B6 and Number of dues in cell B7. I need the column from B11 : B34 to be filled with due dates for every month. Suppose if the loan date is 12/05/2013 and number of dues is 10 then in B11 I should get 12/06/2013 , B12 it should be 12/07/2013 and B13 should be 12/08/2013 and so on based on the number of dues.
View 3 Replies View Relatedfinding a excel formula. For example if i have 1000 in cell A1. I need the result as "One Thousand" in cell B1.
finding the formula for the above mentioned example.
I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.
Atm, it reads some like this
I have attached a spreadsheet : Excel Question.xlsx
I would like to add up in "F9" all the numbers in Column F (F4:F7) which have the name Capital Contributed or Recallable Distribution in Column A and are up to the date of B1 in column B.
Also I would like that excel puts an "X" in column G as soon as it either says Capital Contributed or Recallable Distribution and the date is up to the date in F1 in order to have a macro? that hides everything which has no x.
There should also be a show macro to undo it and show everything .
Another spreadsheet I use has a score within a text and i want to know how I can add up the scores easily.
In the example below I want to know If a Formula can return '9 out of 12'. I know this looks easy so why bother, but my sheet has far more rows/columns of data.
I thought of using Left and Right formula's to strip the numbers out.
Name
Score
A N Other
3 out of 4
A N Other
2 out of 4
A N Other
4 out of 4
I know I can use text to columns to do this. I have horse results listed in column A as follows 3.5L (i.e. 3.5 lengths), 4L and so on. Every number ends with an L. I want a formula in column O that just gives me the number as below:
3.5L 3.5 (in column O)
4.5L 4.5
and so on.
In A1 I have the following text: Distance: 457 meters
I want a formula N1 that isolates the numbers so it shows 457 only
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies View Related I have the following SUMPROCUCT in place and it works fine for current purpose:
=SUMPRODUCT(VLOOKUP($E$9,Rota!$C$4:$FU$369,{5,30,53,78,103,137},FALSE))
columns 5, 30, 53, 78, 103, 137 all contain text. However I need to amend some of these to text but I still need the SUMPRODUCT to add up.
Is there a way to amend the above formula so its counts both text and numbers in 5,30,53,78,103,137 ?
I have the below list of numbers that I need a formula to convert to text and add zeros in the front of the numbers where there are not 6 digits.
207253
197706
8465
9085
102169
19667
183799
183297
Desired Results in Text Format:
207253
197706
008465
009085
102169
019667
183799
183297
Can numbers and text be included in the same cell and still have the number be included in the total in a formula in another cell? Or must a cell only have numeric values for it to be seen/included in a formula's total value.
I'm trying to create a database that totals materials for a construction project. I want to display the number of doors for a house in a row of cells and have the all the doors totaled in the last cell. This I have no trouble doing.
The problem arises when I want to add some text information about the style of each door in the same cell that the number of doors is shown. As soon as text information is added to a cell that has numeric information, that cells numeric information is not included in the final total in the last cell in the row.
I resorted to using comments instead, but, when the are made visible on the spreadsheet, they don't seem to lock to a relative position regarding the cell they're attached to. For instance, if I widen columns or make any significant spatial changes to the spreadsheet, the comments don't move with the changes.
There may be a way to lock comments to stay in a relative position regarding the cell they're attached to. And if that's the only way to make comments for the items in each cell stay with the cell, then I'll have to use that method. But I'd rather not have to use the comments function at all.
I'd much rather be able to have numbers and text be in the same cell, and still have the number value of that cell be included in a formula total at the end of a row of numeric information.
Example: (In this example separate cells that include both numeric values and text are indicated by parenthesis. The final cell that has the formula that totals the numeric information in the separate cells is indicated by brackets)
(30, raised panel doors, unpainted) (10, raised panel doors, white)
I have attached a file which shows some cells which start with "p" and then a number and some have the same but with the word " total" in them.
I would like to run a formula in the column next to it which will highlight which cells have that word in order that I can data sort a large file and delete the totals.
I think it will be an IF formula on cells that contain criteria.
I have to fill out forms for work and some of the info is the same and isd put on seperate sheets. On sheet one I have cells with the information that is the same on the different forms on shhets 2 and 3 i have the forms.
here is what i have having problems doing.
12-47136569
13x129077
How can i do a formula that copies the above values exactually to a cell ona another sheet
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
=SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,""))
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
I have data in the (various) formats as follows:
1) Text value here
1. Text value here
1.Text value here
1 Text value here
1)Text value here
Is there a way to take the cell contents from the first Alpha character? (i.e. no punctuation, no numbers). There are other numeric characters in the text values that I would like to keep.
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
I'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation.
OK OK to illustrate it a little better:
There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this:
A B
1 Bill 10
2 Ted 20
3 Andy 30
Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
I am trying to write a formula which has text and then a date which is taken from a different sheet in the workbook.
EG the text is "Nav as at 31st March 06"
With 'Nav as at' being the text and the date 31st March being picked up from the other sheet.
However the date on the other sheet is the following month, ie 30th June. SO I would need it to pick up the End of the month and minus it by one month and add it to the text.
I have tried several methods but none seem to work!
I've tried a search on this, but have not had any luck. On my spreadsheet, Column B contains text split by a forward slash:
I.e.
123/4567
I am trying to write a formula in column L to display the information on the left of the "/" as well as a formula in column M to display the information on the right of the "/". Normally I would just use the Right/Left worksheet functions, however the number could vary from 2-5 digits on each side of the slash.
I am continually getting data withthe format YYYY-MM however I cant get excel to recognize this as a date. i simply need a formula that converts the format to anything that excel will recognize as a date so that I can apply some other formulas to it.
View 9 Replies View RelatedMy formulas is
="Start Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,19,0) & "
End Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,20,0)
The results I want are Start Date: MMM-DD-YYYY End Date: MMM-DD-YYYY
What I am getting is: Start Date: 41537.7083333333 End Date: 41537.7083333333
So excel is give me the numeric values of the date instead of the formatted date. I tried to format the cell as date, but as I expected that didn't work because the cell also has text in it.
I am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
I have a document that I downloaded some data from the Internet "Kronos" and within this data there is a column for date. This column is formatted as Date (01/08/13), but it only displays the date as Sun 1/01. It's missing the year, which it should be 01/01/12. I have tried to use TEXT function to convert it to month name and day name using TEXT(N4, "mmm") and Text(N4,"ddd"), but I still getting Sun 1/01. I have also tried to use Text to columns to split this date but, it does not work. Is there any other way I can convert this date into m/d/yy?
View 2 Replies View Related