If Two Cells Are Blank Return Blank - If One Has Date Subtract Today Date
Apr 24, 2013
Using 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
View 3 Replies
ADVERTISEMENT
Dec 31, 2013
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
View 3 Replies
View Related
Apr 4, 2008
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 ...................
View 4 Replies
View Related
Nov 14, 2013
I 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] .......
View 6 Replies
View Related
Apr 25, 2014
I'm in Excel 2010, and the cell with the date I want to work from is G22e?
EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
EDIT #2: I guess I should probably note that the date in H22 is the result of another formula
=EDATE(G22,12)
View 7 Replies
View Related
Sep 28, 2009
=Date(year(today()),month(today()),A1) works great, However I need the formula to subtract 1 month if the value returned would be greater than today's date.
View 2 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
Dec 9, 2009
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View 5 Replies
View Related
Apr 26, 2008
I 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)
View 5 Replies
View Related
Dec 9, 2009
I have a simple date formula that I want to present "yes" in a cell when another cells date is less then or equal to "Today". I just can't figure out how to make it show my result cell as blank if my date cell is blank?
here is my formula....
=IF(G8 <= TODAY(), "YES", "")
View 2 Replies
View Related
Oct 31, 2008
I am trying to add/subtract dates and times to return one sum; but I only want to count the work week (5 days M-F) and work day (8 hrs.) 8am- 4pm. This is to compute the actual turn around time for a certain process.
i.e.:
Date ReceivedTime ReceivedDate ApprovedTime Approved10/1/20081:00 PM10/15/20089:00 AM
The total hours this process should equate to is: 9days and 6hours.
way to achieve this sum?
View 9 Replies
View Related
Jan 30, 2014
see the attached workbook with two different sheets (same data) using different formulas. Each has problems (red text) preventing me from moving forward.
I need to calculate time in level 1, time in level 2 and total time for each row and then be able to sum for the month. Unfortunately, sometimes there is missing data, but I still need to calculate everything possible using a consistent formula that can be applied to the column/row universally.
View 3 Replies
View Related
Dec 15, 2011
This might be pretty tricky. I'm trying to create formulae that can find non-empty cells in a list, and separate by date.
Example:
The following are given:
DateItem
1/23AAA
1/24
1/25BBB
1/26
1/27
1/28
[Code]....
I would like:
List 1 (Jan) List 2(Feb)
AAA BBB
BBB CCC
The original equation was for finding non-blank items without separating by date was given to me below Hopefully it's much easier with it: [URL]...
View 1 Replies
View Related
Jan 17, 2013
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
View 4 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
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
Nov 19, 2009
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
View 9 Replies
View Related
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Mar 27, 2014
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies
View Related
Apr 3, 2014
in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies
View Related
May 2, 2014
I have a cells with a date and time in each cell. I want to subtract the number of days between the cells only. Is there a way to do that with the time in the cell? If not, how do I remove the time in each cell?
View 5 Replies
View Related
Nov 5, 2007
I'm in the process of creating a budgeting spreadsheet for monthly expenses. I have one column (D) as "Paid" and column (F) as "Date Paid". Is there a formula that can automatically insert today's date of entry into the "Date Paid" column, once the "Paid" column has been filled in with an amount?
For example: I enter $20 in the "Paid" column, then the "Date Paid" column is self populated with that particular days date. I would like to do this for every sheet.
View 9 Replies
View Related
May 29, 2014
I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.
[Code] .....
Attached File : Staffing Report 1.90.xlsm
View 7 Replies
View Related
Apr 8, 2009
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View 3 Replies
View Related
May 19, 2009
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
View 2 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 Replies
View Related
Sep 18, 2008
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
View 14 Replies
View Related
Jul 4, 2010
Is there a non-array way to return only the non-blank cells from a range? At the moment I use below formula, but it slows down the file considerably ...
View 14 Replies
View Related
Mar 21, 2013
I have a spreadsheet where cells L4 W4 have headers (no spaces in this range), e.g. Cust1, Cust2, Cust3 etc... etc...
Column C contains product names ....
Each product will have a value under each heading (but not all), e.g. Product A = Cust1:10 Cust2(blank) Cust3:4 Cust4(blank) etc... etc...
What I need is a formula that looks along the row for each product and returns the header name if there is a value. Therefore, in the example above the formula result would be Cust1Cust3 ....
View 2 Replies
View Related
Dec 14, 2012
I'm working with an excel worksheet that uses a TFS plug-in to retrieve tree-structured data from a TFS database. Each item has a "Title" depending on it's position in the tree, in some cell between "Title 1" and "Title 9" inclusive.
Currently I display the title in a new column on the same sheet as the TFS data using the formla below. Since custom columns do not automatically get applied to new items in the TFS list after refreshing, I have to click in a cell and apply to all rows everytime I refresh.
On a different sheet I have a pivot table which contains the work item "ID". What I'd like to do is just be able to retrieve the title for each work item in a column on the sheet with the pivot table, using index/match to find the row, and then return the title from the first non-blank title cell.
Current formula on the TFS sheet (note that '@' indicates "this row", and I've replaced the long table name with "TFSData")
Code:
=IFERROR(
INDEX(TFSData[@[Title 1]:[Title 9]],
MATCH(TRUE,
INDEX(TFSData[@[Title 1]:[Title 9]]"",
1,
0),
0)),
"")
View 3 Replies
View Related
