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


ADVERTISEMENT

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

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

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

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

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

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

Formula For Displaying Next Quarter Date Based On A Date

Mar 22, 2012

What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.

Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.

The formula I have got to work is

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)

but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER

when I change the formula to

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)

The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?

View 6 Replies View Related

Displaying A Certain Date

Jan 12, 2009

Is there anyway I would be able to display the current age of majority (19 here at home) in a cell or userform? This would have to be current each day the form or sheet was opened.

View 5 Replies View Related

IF,AND, Function Displaying Last Date

Jan 23, 2010

I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).


I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".

View 5 Replies View Related

Cell Displaying Date

Nov 12, 2009

Each time my spreadsheet is modified and saved, I would like the date of this action to appear in a cell. I think this may involve VBA but I'm not sure.

Let's just say, the cell is L54. I am using Excel 2007.

View 9 Replies View Related

Displaying Cell Depending On Date

Apr 24, 2007

My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner.

View 9 Replies View Related

Matching Two Sets Of Date And Displaying

May 8, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets and if they match enter in column D of the referral sheet the month they were seen but only if its a 1st contact (appt type on column D of contact sheet)

ABCDReferralIDReferral Date409383316 January 2012 Month / year of referral Month of first contact
485955005 March 2012January 2012539372005 September 2011March 2012385048229 February 2012
September 2011483172202 December 2011February 2012944156617 January 2012December 2011
393039322 February 2012January 2012

ABCDContact IDcontact Datemonth / year of contactAppointment Type
539372026 January 2012January 20121ST385048205 March 2012March 20121ST944156616 December 2011
December 2011F/UP222944119 December 2011December 2011F/UP344055529 December 2011
December 2011F/UP539372010 September 2011September 20111st

View 8 Replies View Related

Serial Number Date Displaying Incorrect Day?

Dec 16, 2008

I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm.

I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review.

What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre!

I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.

View 6 Replies View Related

Displaying Date And Time At The Moment Of Entry

Dec 12, 2011

I have cells on my spreadsheet which contain: =now() and they work as they should - displaying the date and time at the moment of entry. Unfortunately the cells update when other info is added at other locations on the sheet - is there a way to ensure they enter the current 'now' only at the point of initial entry - such that the data doesn't then alter?

View 3 Replies View Related

Sorting Duplicates And Displaying Latest Date?

Dec 23, 2011

I am working with a large spread sheet of people names and the courses that they have attended and what date they attended. The Sheet Identifies : ID No. Names, Courses, Date Attended, Due Date How can i remove all entries execpt for the last date for each qualification entered on each person?

ABCDEID. No.Name CourseDate AttendedDue Date22SmithFirst Aid2010/10/102011/10/1022SmithFirst Aid2011/10/102012/10/1021BobECDL2010/09/092011/09/0921BobECDL2011/10/102012/10/1022SmithECDL2010/08/082011/08/0822SmithECDL2011/09/092012/09/0920TerryFirst Aid2010/09/092011/09/0920TerryFirst Aid2011/10/102012/10/10

View 5 Replies View Related

Displaying What CAMPUS A Student Was LOCATED At On The Course Completion Date?

Jul 29, 2014

I have a spreadsheet containing 2 worksheets. First worksheet has my Students Details and the second spreadsheet has their Campus Locations.

What I need to create is a Formula that tells me "What CAMPUS was the student LOCATED at on the Course Completion Date?"

[URL]

what I'm trying to do is as follows:

John Completes his Course on the 25th July 2013, his Student ID is 450.

I need to go to the CAMPUS LOCATION spreadsheet and first search for Johns student ID (450) in column B. Because John is listed in B2,B3 and B4 excel needs to check date in Column E to see if his completion date was before the Date he started at Campus.

If it is then the Campus he was at will be the value of Column D on that line.

If it is not then it needs to search the next line until it satisfies the condition.

View 5 Replies View Related

Displaying Last Modified Date And Time Of File In A Cell

Jul 11, 2009

How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.

This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".

View 8 Replies View Related

Dates Before 1900

Sep 3, 2009

I'm an accountant, so I've always dealt with relatively current dates. Now I'm trying to do some geneology work, and I find that I can't put in a date prior to 1900. For example if I input "3/5/1870", it comes out as text. If I input "=date(1870,3,5)", it returns 3/5/3770 -adding 1900 years to my date. I don't need to do anything mathmatically with it, but it would be nice to have it return "March 5, 1870".

View 2 Replies View Related

Displaying MIN Date Based On Criteria In Calculated Field Of PowerPivot?

Jan 23, 2014

I have a database with a list of transactions with multiple fields, including:

Customer ID
Store ID
Item ID
Date
Price

I am wanting to add a calculated field that displays the first (or MIN) Date from the date column, looking at all of the orders where the customer ID, store ID, item ID and price are the same.

In the attached file I have shown what I currently have, as well as a solution using a helper column, and formulas I would use if I wasn't using powerpivot.

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

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

Displaying Values Used In Criteria Selection (Date Ranges) As Part Of Query Output

Dec 20, 2011

Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:

SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName

The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).

However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.

View 3 Replies View Related

VB Code To Remove 00/01/1900 Dates From Spreadsheet

Feb 6, 2014

I have a spreadsheet which was formatted from VB code. Within the sheet there are four date columns where some dates show as 00/01/1900 (due to them having '0' in at the time of running the code). I am trying to run some code to blank out all cells showing the 00/01/1900 date, but the code just isn't touching it. I reverted to simply recording the 'find and replace' function and inserting into my code, but those irritating dates still remain.

Code I have used is as follows:

[Code] .....

View 4 Replies View Related

Displaying Cell Date As 3/14/2001 With Text In Cell

Apr 23, 2012

I have a Cell and the Formula for my Cell is the Following:

Code:
="Week Ending " & 'A01'!D248

The A01 Worksheet displays the value as 4/21/2012 . However, the first worksheet displays the value as

Code:
Week Ending 41020

rather than

Code:
Week Ending 4/21/2012

What can I do to display the cell the way that I want to? I've already tried formatting the cell directly as Date to no avail.

View 3 Replies View Related

Displaying And Un-Displaying Objects Using VBA..

Apr 21, 2007

Is It Possible to use VBA Coding to Display An Image (Object),
And Un-Display or Remove that Same Image (Object) using a VBA Code?

Example:

I have a Mailbox Picture that I would Like to Show, but only when a cell's
value = 5. If the Cell's Value is not 5, then don't display the mailbox.

View 12 Replies View Related

Displaying Cell Date With "&" In Another Cell

Dec 18, 2008

I'm trying to get excel to display a quote, plus a cell's text/value... the problem I am having is that the format is a date, and it ends up showing something like 37896 instead of 11/25/2008 (those are examples, not actuals). Here's the

View 3 Replies View Related

Displaying Zero

Nov 24, 2009

I have tried different formula involving with decimal places.

If A1 has a whole number like 5141234 then the result would come out as a whole number =(A1-5148000)*1.000440935+48000

If A1 has three decimal places like 5142356.654 then the result would out as three decimal places

View 4 Replies View Related







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