Date Formula To Display In Text Without Numbers

Dec 18, 2009

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.

View 9 Replies


Create Formula To Display Text With Date

Feb 16, 2007

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 Related

Formula To Get Date In Text Based On Numbers

Jan 25, 2014

I 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

View 2 Replies View Related

Dates Display As Numbers Even With Date Format

Jun 20, 2008

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.

View 6 Replies View Related

Excel 2013 :: Making Numbers Display With Color Based On Text Inputted In Different Cell?

Nov 20, 2013

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.

View 3 Replies View Related

Display Date Upon Last Formula Change?

Dec 19, 2011

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 Related

Formula To Display Same Date For All The Months

Jul 31, 2013

I 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 Related

Excel Formula To Display Text?

Oct 1, 2013

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

View 1 Replies View Related

Using VLOOKUP To Display Date If Present, If Not Display Today's Date

Feb 26, 2009

I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.

Atm, it reads some like this

View 3 Replies View Related

Add Up Numbers With Certain Text And Date?

May 6, 2014

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 .

View 5 Replies View Related

Can Formula Be Used To Add Numbers Within Text?

Jun 30, 2014

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.


A N Other
3 out of 4

A N Other
2 out of 4

A N Other
4 out of 4

View 9 Replies View Related

Formula To Separate Numbers From Text

Oct 28, 2012

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.

View 3 Replies View Related

Formula To Isolate Numbers From Text?

Oct 28, 2012

In A1 I have the following text: Distance: 457 meters

I want a formula N1 that isolates the numbers so it shows 457 only

View 3 Replies View Related

Formula To Add Numbers And Exclude Text Values

Apr 1, 2009

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

Amend Formula So Its Counts Both Text And Numbers?

Apr 23, 2014

I have the following SUMPROCUCT in place and it works fine for current purpose:


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 ?

View 2 Replies View Related

Formula To Switch Numbers To Text And Add Zeros

May 12, 2014

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.


Desired Results in Text Format:

View 2 Replies View Related

Numbers And Text In Same Cell & Tally In A Formula

May 10, 2008

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)

View 9 Replies View Related

Formula To Highlight Text Within A Cell Which Also Has Numbers

Jun 28, 2006

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.

View 4 Replies View Related

Copying Cells With Text And Numbers To Another Workbook Using Formula?

Dec 10, 2013

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.

How can i do a formula that copies the above values exactually to a cell ona another sheet

View 1 Replies View Related

Array SUMIF Formula To Sum If Numbers But It Not Place Text?

Feb 10, 2014

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

View 5 Replies View Related

Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.

Dec 29, 2008

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

View 5 Replies View Related

Formula To Remove Preceding Numbers From Text Input

Mar 5, 2012

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.

View 5 Replies View Related

Excel 2010 :: Formula That Will Work With Text And Numbers?

Dec 10, 2013

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

View 4 Replies View Related

Formula To Rank Text Objects Based On Numbers, Interesting Read

Dec 30, 2008

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:


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.

View 9 Replies View Related

Date And Text In One Formula

Jun 23, 2006

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!

View 10 Replies View Related

Formula To Display Text Left Of "/"

Jul 27, 2006

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

View 9 Replies View Related

Formula To Recognize Date As Text?

Dec 9, 2013

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 Related

Formatting Date In Excel Formula With Text

Mar 6, 2013

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

View 2 Replies View Related

Formula To Change Date In One Cell Based On Text In Another?

Jan 29, 2014

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;


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.

View 7 Replies View Related

Date From Internet Does Not Display Year And Can't Be Converted To Date

Jan 8, 2013

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

Copyrights 2005-15, All rights reserved