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