I have a worksheet with a range of dates in column E. What I would like is a formula that states If any of those dates is less than or equal to 10 (days) from today and column H text is not "Yes" then show text "Email outstanding Actions" else show nothing?
View 4 RepliesI have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip.
Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.
I have been trying to write the if statement for four conditions. It works well for first three but does not return text value "N/A" for fourth. See example below.
=IF(S2=M2,$M$1,IF(S2=N2,$N$1,IF(S2=O2,$O$1,IF(S2=0,N/A))))
MNOST
GL_WeldBendHeaderMax Max
0.0170.011510.0005Bend0.01151Bend
00.000780Bend0.00078Bend
00.000320.00001Bend0.00032Bend
000N/A0GL_Weld
00.000610Bend0.00061Bend
000N/A0GL_Weld
0.000020.000440Bend0.00044Bend
000N/A0GL_Weld
So if the S2=0 it should put "N/A" instead of GL_Weld
I'm trying to create a SUMIF-style formula which is able to test for more than one condition. i.e if the values in column C meet a certain criteria and if the values in column E satisfy another criteria then add all the corresponding values in column D. In my particular spreadsheet, column C contains a date value. So, for all the dates in column C which are later than 01/01/2008 and for all the cells in column E which contain the word "oustanding", I need to add up all the corresponding values in column D.
View 3 Replies View RelatedI have an infinite list of consecutives dates on a row like the one below. I want that everytime I open my file, the first column shown is the one containing today´s date. If today´s date is 16.Feb, then the first column must be the one containing 16.Feb and so on.
15. Aug.16. Aug.17. Aug.18. Aug.19. Aug.6060606060
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................
Is it possible to have a If statement based on the cells text color.
ie
If(color of A2=RED,1,2)
So if Apple text was colored red the result woud be 1
If the Apple text was black the result would be 2
I wan to set up an IF statement so that if the date in Column A is between 4/28/14 and 7/17/14, it will return "Q3" and blank if no date.
View 1 Replies View RelatedI'm having a bit of trouble with a macro designed to read the length text in a cell and if it = a certain length then perform an action (in this case Text to column)
Here is a small sample of the data I'm working with:
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
*End Sample*
The text length I want it to perform the action on is highlighted with "<<" if the length of text does not meet the required number then I want the statement to skip and move onto the next one.
I have the text to column code already done with relative references however the long text string I want the statement activated on is not always present which means that the pattern (0,3) is not always consistent.
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
I have a data of 10 rows and 6 columns, if i delete the data in column 1, I want the entire row 1 to get deleted and this I want using IF Statement.
View 2 Replies View RelatedI want to highlight a cell if the text displayed from an IF formula is equal to the cell content.
CELLS BK10:BN10 are merged and have entered into them the following text "SELL"
Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"
=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))
Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%
When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?
I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
I’m trying to resolve this issue and would appreciate some help.
Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!
Cell A1 contains a Date eg (01/01/07)
In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
View 7 Replies View RelatedCol C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date
range in Col F.
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies View RelatedI have 2 columns: Locations (Column A) & Dates (Column B).
I want to find the most recent date for a selected Location (stored in $G$1).
currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).
I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.
Is there some way to condense this into just 2 cells?
See attached file for example (note the columns are currently sorted by date, but that is not always the case).
I have the following code that fills a range of cells (starting at the active cell) with the date a user selects from a calendar control.
Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)....................
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"
I am using MS excel 2007.
I have a file that has start date column. I'd like to show the account's expiration date on another cell but it's going to be dependent on another column.
Say
Column A - customers name
Column B - start date
Column C - rate
Column D - End date
Column E - Follow up Date
Rate column will have the ff inputs :
rate 1 = .09
rate 2 = .11
If rate is .09, then end date will be 2 years from the start date
If rate is .11, then end date will be 3 years from the start date
There are several things I need to happen :
1. Show the customer's individual end dates in Column D
2. I'd like to show in column E the follow up date which is 90 days before the end date
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:
=IF(D3="", IF(B3=";", FixedDate(),B3))
When I enter data into cell D, I get the following result in B:
0-Jan-00
Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??
I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.
I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.
However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?
02/07/2012
14/08/2012
43
00/00/0000
#VALUE!
[Code]...........
Im having trouble with a combo box with a range of dates as the drop down list.
The Named Range it is connected to is as below
01-01-10
02-01-10
03-01-10
Etc
But when i run the user form the Combobox drop down box show as below
40179
40180
40181
Etc.
When i Select one the combo box does then Show the list in date format
How can i make the dropdown list show in date format also below is the code i currently have for the combobox
Private Sub ComboBox4_DropButt*******()
ComboBox4.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Date").RefersToRange)
ComboBox4.Value = Format(ComboBox4.Value, "dd/mm/yy")
End Sub
I am trying to write a formula in a cell that sum the Total/Day and the Total/month and graph it based on Date
example:
-----Date-------- Sale------Total/day-----Total/month
1---10/10/2012 ---$5
2---10/10/2012 ---$7 ------$12
3---10/13/2012 ---$2 -------$2
4---10/17/2012 ---$8 -------$8 -----------$22
1---11/3/2012 ---3
2---11/3/2012 ---5 ----------8
3---11/7/2012 ---6 ----------6 ------------14
1---12/15/2012 ---2 ---------2
2---12/25/2012 ---4
3---12/25/2012 ---2 ---------6 ------------ 8