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 RepliesThree 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?
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.
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
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.
I am creating an IF formula that will produce the current date in one cell if another item has been entered in another cell.
For example
cell g (accounts) is blank, cell H (date found).
Once the correct account is entered into cell g the date will autofill in H.
The formula i was using is =IF(G1"",TODAY(),"")
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.
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 RelatedI 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 RelatedHow 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 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
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
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 RelatedUsing one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
I would like excel to automatically suggest the save as file name and location for my workbook when I click the save, save as or close options. The file name should be a combination of values in cells A1 and A2 of Sheet1. The location should be a folder named "Bill" in C:/My Documents. I think I can do it with a beforesave code but I am new to vba.
View 13 Replies View RelatedHow do you produce a drop down menu in a single cell in Excel
View 9 Replies View RelatedI am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
View 14 Replies View RelatedI have a column that indicates the earliest date that a project will start. There is a Project Minimum Date, Activity Dates, Sub Activity Dates and Sub Sub Activity Dates.
When a Sub Sub Activity Date is entered, it goes up to the Sub Activity Date and so on.
I have a formula =SMALL($E6:$E224,COUNTIF($E$6:$E$224,0)+1) that will select the earliest date out of the sub sub activities and sub activities but if one of the cells is blank in the sub activity or activity, then a #NUM! error is returned.
I have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.
View 2 Replies View RelatedI have a date range, containing blanks and values. I need a function to show the earliest date that contains a non-blank value. The function should be robust enough that I can sort by date and still show the correct data.
Example...(correct answer would be 10/26/2006)
Date ... Value
10/23/2006
10/24/2006
10/25/2006 0.08
10/26/2006 0.10
10/27/2006
10/28/2006
10/29/2006 0.18
10/30/2006 0.20
10/31/2006
11/01/2006
11/02/2006
11/03/2006 (0.18)
11/04/2006 (0.15)
I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.
To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))
I tried adding as an extra part, for example:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)
It always brings back the answer of 366, and does not make the cell blank...
I was trying to use a modified version of JBeaucaire's code to achieve the same results within my form. In my workbook I have a a table (called Table27) that ranges from A7:CL109
This is the code I'm attempting to use:
[Code] .....
Column A is where my target cells are... where the user will enter data. In cell CI (the 87th column) I want the Data & Time stamped.
I thought I'd modified the formula correctly, but I can't seem to get it to work.
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 RelatedI'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 RelatedI would like to highlight (conditional format) the "response required by" (Row A) cells / dates if the "current date" (cell $B$2) is exceeded AND if the "response provided By" (Row B) remains BLANK:
Current Date
=Today()
Response Required By
Response Provided By
29-10-2013
23-10-2013
[Code] .......
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
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] .....
On A3: D10 I have information on C1 a have date and E3:E10 I will input date
i.e. If the date entered in E3 is the same as or larger than the date on C1 then Blank the entire line A3: D3
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View Related