Conditional Formation Based On Month
May 4, 2009I have a field where the month is written as text. I would like to highlight row whose month column has passed. My conditional formating code is:
View 4 Replies
ADVERTISEMENT
Conditional Formation More Then 3 Conditions VBA
May 1, 2009I have some data in cells C5:G50
I can then enter some data in cells C4, D4, E4, F4, G4
What i want now to happen is when the value in C4 matches a value
anywhere in C5:G50 then i want that cell to have green background etc.
I have found this code but it does not seem to work
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim c As Range
If Not Intersect(Target, Range("C5:G50")) Is Nothing Then
For Each c In Intersect(Target, Range("C5:G50"))
Select Case c
Case Is = "C4"
icolor = 5
Case Is = "D4"
icolor = 5
Case Is = "E4"
icolor = 5
Case Is = "F4"
icolor = 5
Case Is = "G4"
icolor = 5
Case Else
icolor = xlNone
End Select
Intersect(Rows(c.Row), Range("C:C,D:D,E:E,F:F,G:G")).Interior.ColorIndex = icolor
Next c
End If
End Sub
Floating Bar Chart In Step Formation
Jul 24, 2007I am trying to create a floating bridge bar chart.
Here is my data and I will try to describe as accurately as I can.
FY 2004 operating margin = 14.6% (while FY 2005 and FY 2006 = 11.2% => So the difference is 340bps)
The "bridge" back to 14.6% = 90 basis points for Jimmy'Z, 30 basis points for stock option expense, 30 bps for marketing -- that's 150 bps of the 340 and I'm trying to quantify the rest of the bridge but is there a way to show this in excel with floating bars - that is to say the 14.6% bar will touch the axis but the other 3 data points are not touching the X axis but are in the air in a step formation... ?
Function To Fill All Days Of Month To End Of Month Based On Workdays
May 1, 2006I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
Conditional Format Of Current Month?
Oct 15, 2012i'm using the following three formulas to perform conditional formatting on cell B when cell A contains a date. Each condition depicts a cell colour depending on the following:
Within The Current Month
=datevalue(day(today())&"/"&month($q$3)&"/"&year($q$3))=(today())
Prior To The Current Month
=datevalue(day(today())&"/"&month($q$3)&"/"&year($q$3))(today())
It took me so time to put this together, but I've one other condition which I need to add to the above which I'm having difficulty in solving.
In addition to setting the cell colour of cell B, i would also like to set a text value.
So if the date is:
Within The Current Month, I would like the cell to say "Act'ls",
Prior To The Current Month, I would like the cell to say "Act'ls", and
Greater Than The Current Month, I would like the cell to say "A'able"
Conditional Format On Month Change
Dec 18, 2008I have created a 4 week custom calendar for planning purposes. As part of this I naturally have cells containing the dates of the days involved. These dates have been customised to show only the day as it is more presentable than including the month. The down side to this is it is difficult to identify when the month changes at a glance. Therefore, I would like to do the following:
1. Change the font colour of the day number when the month changes, e.g. December dates in black, January dates in red.
2. In one cell display the months the calendar refers to, e.g. if December and January dates are in the calendar the cell should read December '08 - January '09; if only December dates were included, the cell would read December '08.
Conditional Formatting With Month And 'Dates Occurring'
Dec 9, 2009I'm trying to keep track of training dates for employees.
I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.
I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.
Is there a better way to do this?
VBA For Conditional Formatting Of Cells With Different Month Values
Oct 26, 2009I have two date columns, say E & F. In these two columns I have entered date values. I need to do colour, font bold, Italic automatically when date values are entered in these columns - with a conditional formatting - i.e. every month should be in different color.
For example, values 23/05/2009, 15/10/2005, 07/12/2004 must be automatically coloured with different colors as its months are different.
Multi Conditional Formula For Year And Month
Mar 22, 2005I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.
************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
Trim Month Year For Conditional Formatting
Feb 7, 2007Having trouble with a date format:
I am trying to get CurrentDate from a 02/07/07 to 02/07
My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy
My eyes are watering from stareing at the screen for so long....
Private Sub TextBox24_Change()
Dim MonthPlus, CurrentDate As Date
Dim userow, usercolumn As String
Multiply Cell Without Copying Cell Formation
May 16, 2014I want to use the little dot in down right corner of the cell to copy the cell content and the formula correctly.
But now the problem: every time I use it also copies the cell formation.... Is there a way that it only copies the content, but not the formation?
Conditional Formatting - Calculate The Numbers Of Month And The Revenue?
May 5, 2014i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies View RelatedAverage With Conditional Month, Year And Date Range
May 8, 2008I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).
As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
Dec 23, 2008I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
Conditional Formatting Based On Another Cell Conditional Format
Mar 20, 2013Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Auto Populate Calendar Days Based On Month / Year And Auto Insert Work Based On Dates / Name
Jul 31, 2013I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
Conditional Percentage Based On Conditional Sum
Mar 13, 2008I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
Countif Based On Month
Dec 16, 2013Below is my function. I use it to count the number of times a value in A9 appears in Sheet2. I would like to edit my function to count the value in A9 that appeared in a given month in U6.(U6 is 12 for decemeber)
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),"")
This is what I thought it would have been but I think I'm wrong because the function is not counting by month.
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),IF(MONTH(Sheet2!D14:D9000)=U6,"")
SUMIF Based On Month?
May 30, 2014I'm trying to use the SUMIF function based on a specific month. This month formula has to choose from a range of dates. Currently, the month to report (5) is the only one that works. If I have dates in June and choose 6, it doesn't work.
This is the formula I'm using right now:
=IF(MONTH('Drafting Errors'!C:C)='Error Metrics'!C1,SUMIF('Drafting Errors'!A:A,C5,'Drafting Errors'!N:N),"No data for this month")
Sum Based On Month Of Corresponding Dates
Aug 20, 2008I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far:
=SUMIF('2008'!E4:E149,(LEFT('2008'!E4:E149)="06/"),'2008'!I4:I149)
My table looks something like:
Log NoShip DateQty
000953/26/20085
000963/28/20081
000974/28/20081
000985/28/20082
000995/29/20081
001006/1/2008
001016/14/20084
001026/20/20083
The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.
Sum Based On Month Criteria
Sep 24, 2007I maintain a table with projects and their respective costs / revenues.
I have a formula that automatically sets the forecast and Year-to- Date periods based on the month and date.
I need to automate the year-to-date sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of Jan-Sept in the YTD column(for this month).
A sample workbook is attached.
Data Based On Month Selection?
Jan 11, 2014I have attached a sample excel sheet.
I want data to appear on sheet 2 from B10 onwards based on month selection and removing duplicate entries and sorting in ascending order
When no data is found to display message box "data not found"
Calculate Sum Of Values Based On Month
May 21, 2014I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.
Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.
Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.
Daily Average Based On Day Of Month?
Mar 2, 2014I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
Return Month Based On Day Of The Year?
Mar 27, 2014I have a sheet with the following values:
280315 085B 10:24 2R
The '085' is the 85th day of the year. I would like a formula that reads that part of the data, and return the month that day is in, in the "jan", "feb", "mar", etc. format.
SUM A Range Of Sales Based On Month
May 6, 2006I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month
For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on
Counting Entries Based On Month
Oct 21, 2009I have a column of dates and want to count how many times there is an entry for a particular month. example:
(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009
I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.
=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.
Counting Record Based On Month?
Oct 25, 2011i have big sheet with lot of data and in column B there is date dd-mm-yy.
How can i count specific product (column A) based on date (column B) for each month) on sheet B.
Autofill Last Day Of Month Based On Another Cell
Jul 25, 2012In cell A12 the format is mm-###. Based on the mm (2 digit month) in cell A12, I would like cell B12 to auto fill the last date of the month as mm/dd/yyyy.
View 4 Replies View RelatedAverage Based On Criteria :: Within Each Month
Jul 29, 2008I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.
So column A = age
and column B = cancel date
Let's so for January 2006 I'd like to find the average age.
Logically this is how I see it...
Average Column A (If column B >= 1/1/2006 and < 2/1/2006)