Display 'Date' Cell As Blank Instead Of Default Year 1900

Nov 19, 2009

Three columns.

A - Date last checked
B - Due Date
C - Actual Date checked

Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?

View 5 Replies


ADVERTISEMENT

Produce Blank Cell If Date Is 00/01/1900

May 10, 2010

I have a column of data that requires the cell to be blank if the value is 00/01/1900. The data is linked to another cell. IE: cell E3 is linked to cell $D$3 The cell is formatted for dates in the format shown.

View 7 Replies View Related

Current Year In Cell Not 1900

Jan 15, 2009

I've conditioned formated some cells so that someone can type a number in and a date will appear in the cell.

Problem is if someone types 20 in the cell.. excel reverts back to Jan-20-1900

How can I get the current month and year when a number is typed in the cell?

Did excel exsist in the 1900?

SOLVED
Solution - I used the if command in A2 like this

View 6 Replies View Related

Set Default Month And Year In A Cell For Entering Date Fields

Jul 26, 2013

I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.

My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that

If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.

View 2 Replies View Related

Date 1/0/1900 Displays Instead Of Blank

Feb 8, 2009

The result of my formula should be blank, but 1/1/1900 appears instead and I can't get rid of it. Does anyone know why or how to fix. I can't just change the font color as it screws up other formulas.

=IF(ISNA((VLOOKUP(VendorTracking!$P10,LeadTimeVlookup,2,FALSE))),"",VLOOKUP(VendorTracking!$P10,LeadTimeVlookup,2,FALSE))

In this case the Vlookup is referring to an apparently blank cell with no formula in it. I googled 1/0/1900 and saw a few references that said formatting a cell with zero in it as a date would cause this result, but there is no zero in this case.

View 9 Replies View Related

Blank Date Cell Being Treated By Default As January

May 24, 2006

I need a formula for identifying the last cell, of every third cell, that has an numeric entry, of a row. For example, in a range of (F7:V7), I need a formula that examines F7,I7,L7,O7...., and displays the last cell that has a numeric entry, while ignoring the other cells, ie G7, H7, J7, etc.

View 8 Replies View Related

Removing The 00/01/1900 Date When Looking Up Another Cell In Another Worksheet

Apr 12, 2009

I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2

View 9 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

Date Display Like Day/Month/Year

Aug 17, 2009

Im having a little issue with the way the dates are layed out.

I have a condition format =AND(RC5="No",TODAY()<RC4+10)

And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works.

Though i have always written dates like Day/Month/Year.
ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.

View 6 Replies View Related

Display Current Year With Renewal Date

Mar 10, 2014

I currently have a spreadsheet with 500ish renewal dates. I need to be able to take the renewal dates (dd-mmm) in column B and then display the current year with renewal date, if the date has not yet come, or the next year with renewal date, if the date has already passed in column C (dd-mmm-yy).

View 3 Replies View Related

Indexing Dates, Blank On One Sheet Displaying As 00/01/1900

Feb 12, 2010

I am indexing dates from one workbook to another.

There are some blanks in the list that I am using, and I need to the blanks to stay as blanks in the sheet that I am indexing to, however they are displayng as 00/01/1900.

View 4 Replies View Related

Date Defaults To 01/00/1900

Aug 25, 2009

I am working with an Excel file that was created in 2001, has not been updated since.

In the first column on the first tab, is a date field, starting from 12/31/2000.

I try to change this to 12/31/2008, and I get the return date of 01/00/00. When I select the cell, the formula bar indicates 12/31/2008, but the cell shows 01/00/00.

If I change it back to 12/31/2000, it is back to normal. I tried to enter 2/3/2009 in another cell, converting to date format, and the same thing: 0/1/00 (Jan 0, 1900)

This is a US date format.

View 7 Replies View Related

Displaying A Date Of 00/01/1900 If The Value Is Zero

Feb 4, 2008

I currently have to display a large amount of data on a separate report. I have a master sheet which has columns and cells linked to another sheet, then a further sheet which has cells linked to the master sheet. The problem I am getting is when I link some of the date columns using a formula in the 3rd sheet, as this is looking at a cell that has another formula in on the other sheet it is displaying a date of 00/01/1900 if the value is zero, is there any easier way of getting the data to just display a blank as this is messing up the database it is being exported

View 9 Replies View Related

Date Converting To 1/0/1900 On Entry

Jan 30, 2008

I have a old spreadsheet file which I opened up in Excel 2003. It has a few worksheet tabs. 2 worksheet tabs seem identical. I will reference it as Worksheet1 and Worksheet2. In Worksheet1, I enter a date i.e. 1/25/2008 in any cell. It enters as inputted. But in Worksheet2, I enter the same date i.e. 1/25/2008, it appears as 1/0/1900. No matter what date I enter in Worksheet2 in any cell, it always appears as 1/0/1900. Now when I look at cell in the formula toolbar, it sees as a Math Formula meaning, it looks like this: =1/25/2008. How do I correct this Worksheet to recognize any date inputted, not to see it as a Math Formula?

View 4 Replies View Related

Converting 1900 Date System To Text

Jan 30, 2009

How do you convert a range of dates in this format: 39843 (i.e today 30th of Jan 2009). To text like: 20080130. Secondly, For the same date, how do you make the weeknum function give answer '05' and not just '5'

View 2 Replies View Related

Conditional Format Formula: Allow The User To Blank Out The Cell Within The Year

Sep 26, 2009

i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck

View 2 Replies View Related

Date Formula: Date Another Cell Rounds Up To Jan 1st Of The Next Year

Oct 22, 2007

what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.

View 3 Replies View Related

Set Date In Table Cell To Be Date In Another Cell Plus 30 But Leave Blank If Other Cell Is Blank

Nov 19, 2013

I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.

e.g
Started
Finishes

12-Nov-13
12-Dec-13

30-Jan-00

09-Nov-13
09-Dec-13

11-Nov-13
11-Dec-13

Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?

The current formula for cells in the "Finished" column is:

=[@Started]+30

View 2 Replies View Related

Date And Month From A Column And Year Should Take Current Year

May 14, 2009

I have dates in my column A, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)

View 3 Replies View Related

Stop 01/00/1900 Showing In Cell

Dec 20, 2006

I have a cell that has a formula in it and is also Excel Dates & Times coded. Hence when the repsective cells are empty and there is no values to be calculated the date "00-jan-00" shows. how to prevent this from showing up allowing for a blank cell to only show?

View 4 Replies View Related

How To Get Only Month Year If There Is DATE - MONTH - YEAR In A Cell

Aug 20, 2013

How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?

A1: 27-July-2012

Answer D1:JULY' 2012

Pl note "' " is suffixed after JULY (the month).

View 4 Replies View Related

IFERROR Won't Display Blank Cell

Mar 13, 2014

I am using this formula, but if the referenced cell is blank, it keeps putting a 0 in the cell. I want the cell to be blank if the referenced cell is blank.

=IFERROR(INDIRECT($E$1&"!$C4"),MATCH(INDIRECT($E$1&"!$C4"),""))

View 9 Replies View Related

Look Up The Date To See Which Year It Falls In And Return The Year

Oct 13, 2008

i have the following table of information

Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93

and a list of dates i need to look up the date to see which year it falls in and return the year

View 9 Replies View Related

Convert Date From Any Year To Current Year

Feb 27, 2009

DATE function won't return TODAY()'s year in the "year" slot.

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

View 9 Replies View Related

Year To Date Sum By Month In Cell B8

Jul 14, 2007

I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.

View 9 Replies View Related

VBA To Display Combo Box In The Next Blank Cell In Column A

Jul 8, 2009

Been doing pretty good up till now but I can not figure out how to get a combo box in the first blank cell of column A. I am using the named range 'Units' and there are almost 900 of them so I need the combo box.

View 8 Replies View Related

Make A Cell Display 0 Or Remain Blank Instead Of #N/A?

Aug 15, 2009

How can I make a cell display 0 or remain blank instead of #N/A?

I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.

View 9 Replies View Related

Pulling Of Month & Year From Date Cell

Dec 2, 2009

i have attached a sheet, i want if i put date in cell a2 and automatically month should come in cell b2 and year in c2,

LIKE JAN AND 2009 LIKE THAT

View 5 Replies View Related

After Date Occurs, Cell Updates To Next Year

Jan 2, 2010

The following formula is in cell A3: A3=IF(A2="X",A1+365,""). Rather than A3 displaying only 1 year, I would like for it to update after that day occurs to =A1+730, and then A1+1095, and so on.

View 2 Replies View Related

Changing The Year In Date Range From Another Cell

Feb 22, 2007

I am trying to change the year of a date range I search from.

I have a selection of dates, I use sumproduct on a seperate page to calculate the number of entries between two dates entered in seperate cells

=SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2))

Cell B1 conatins the date 01/01/2007, and cell B2 contains the date 31/01/2007. I repeat this for every month of the year.

I want to be able to change the year of these dates in B1 and B2 from a seperate cell on another page.. so I can change the search ranges to 2008 without going into each cell and changing it manually.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved