Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










If 1st Wednes Day Or 3rd Of The Month Then Hightlight The Celll


looking for a formula that will tell me if a cell containing a date
is the 1st wed of the month or a 3rd wed of the month then
hightlight the celll

example
=if(A1=1st wed or 3rd wed,A2=green,"")


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Hightlight Range Based On Cell Value
I would like a macro to run everytime A1's value changes.

The following works for an entire row, however, I would like range A:F highlighted not .entirerow.

I have thought of conditional formatting, but I thought the range I was using was to large. (A3:F40000)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
If Range("a1").Value > 0 Then
Call mymacro
Else
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
End If
End Sub

View Replies!   View Related
Want To Combine Celll Values
I have a sheet in which there ara $ and Cents but in the bottom i want to sum them together. How can i do that?

View Replies!   View Related
Check If Celll Contains Certain Characters
If character or letter "A", or "B", and so on (until "J") is found both in Column A and Column B in a given row, then it is TRUE.

If no character or letter (from A to J) matches in both columns, then FALSE.

**Numbers are irrelevant.

View Replies!   View Related
Conditional Formatting: Count The Number Of Items On The Right And Hightlight Them With A Color
I have attached a copy of my "budget". What i need is whenever you choose a option in A9 on PayCheck - DEC - 09 - B it will count the number of items on the right and hightlight them with a color. I use =COUNTIF('PayCheck - DEC-09-B'!E$2:E$1000,A9) in A11 to tell me the number of occurences but I would also like a visual effect with colors.

View Replies!   View Related
Sort The Lowest Price From Columns And Paste Into Other Celll
I have a list of stores across the ABC columns and a list of items down the number rows.

I need to sort the lowest price from the A2,B2,C2 row and place it in another cell (possibly L2) along with the store name (from A1,B1....) in M2.

View Replies!   View Related
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)
I 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 ...

View Replies!   View Related
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View Replies!   View Related
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data

On the last row of each month, Iím trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

Iím having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but Iím not sure how to make the first array dynamic or if this is even correct approach.

Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View Replies!   View Related
Using Offset From Latest Month To Calculate 3-month Average Within A Range
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........


View Replies!   View Related
Automatically Bold And Highlight The Current Monthís Total And Month Name
I have a spreadsheet for monthly supplies. In row 1 is Jan Ė Dec and in the row 2 below are empty cells where there will be a total for that monthís purchases. I want a conditional format formula to automatically bold and highlight the current monthís total and month name.

Also, when I enter February totals next month and that number is input into Februaryís total, I want that month and total to bold and highlight BUT I also want the previous monthís bold and highlight to vanish at the same time. Is this possible?

View Replies!   View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I 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.

View Replies!   View Related
Formula To Distinguish Month Year From Prior Month Years
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View Replies!   View Related
Attached Worksheet Automatically Shade Out All The Saturdays & Sundays In Any Given Month Everytime You Change The Month/Year Cell
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.

View Replies!   View Related
Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?


View Replies!   View Related
Auto Format Spreadsheet With Various Rows Month To Month
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.

Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).

Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?

I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.

View Replies!   View Related
Adding Or Subtracting One Month To A Month Number
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth-1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January)

Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?


View Replies!   View Related
Function To Fill All Days Of Month To End Of Month
function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for shorted months such as Feb.

View Replies!   View Related
Formula That Compares Month Over Month Data
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.

=IF((C26-B26)/B26

View Replies!   View Related
Month(Date): If The Month Is Not January It Works
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:

View Replies!   View Related
Month To Month Analysis On Monthly Data
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.

View Replies!   View Related
Results By Month And Week Of Month
I have a range of data which is as follows:

Week in month: 1 1 1 5
Site: 01/03 02/03 03/03 etc 30/03 etc
Leeds 10 9 15 20
Manchester 8 5 1 2
Etc

Here's what I need to produce:

March 08 April 08
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6

Leeds
Manchester

I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.

View Replies!   View Related
SUMIF Month & Year: Find Total Cost By Month Only For Year 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

View Replies!   View Related
Formula Year Month To Last Day Of Month, Month And Year
I'm after a formula this time ... i've searched the board and can't find what i need.

a cell shows 2009 December

and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..

View Replies!   View Related
Month And Weekdays Of That Month
I have given up after 2 hours of trying, so here I am again.

I would like the current month to automatically appear in cell B4,
just like using =TODAY() BUT, once the sheet has had data entered into it for that month, the month (B4) cannot then change next month when the spreadsheet opens.

Once the month has appeared in B4, I would then like the weekdays of that month to appear in B7:B30.

View Replies!   View Related
Last Day Of Month Reverts To First Of Month
I'm doing a web query that brings down stock dates/prices. The dates come down as mm/yyyy. I then format the column as mm/dd/yy which changes the date to mm/01/yy. Then I do a loop that converts the dates to the date of the last weekday of the month. I see the changed dates written back to their cells, but by the time I leave the called conversion subroutine and hit the break on the next line of code, the dates have all reverted back to the first of the month.

In a case like this, I use the Step mode in VBE to step through the code while I
have the worksheet the code is acting on visible. That way you can actually see
which line in the code converts the value back to the original value or if indeed
it does put the eomonth date in the active cell. By the way, I'd suggest you use the equivalent of the eomonth worksheet function directly in the macro rather than pasting the date to another cell and reading the eomonth from another cell containing the formula for eomonth.

View Replies!   View Related
Formula "MONTH" And The VBA "MONTH" Return Different Result
When, Cell A1 is blank

1] Formula function : =MONTH(A1)

View Replies!   View Related
Current Month: Column B Equal To The Current Month Adding The Day In Column A
I have the following data:

column a: column B:
1
7
9
25

I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:

column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009

View Replies!   View Related
Year Month Date To Month Date Year Code
Serial No Search †E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4

E - Year Month Date
I need F column as Month Date Year Format

View Replies!   View Related
Month Plus One
A1 enter March

I ws looking for a formula that will populate say H1 with April


View Replies!   View Related
Add EXACTLY One Month To Another Month
I would like to enter a month into a cell (B5) and have each cell below it automatically calculate one month from that date. Here is what kind of result I am looking for:

5/5/2006 -- User Enters Start Date Here.
6/5/2006 -- Rest of dates populated exactly 1 month from prior date
7/5/2006
8/5/2006
9/5/2006
10/5/2006
11/5/2006
12/5/2006
1/5/2007
2/5/2007
3/5/2007
4/5/2007
5/5/2007
6/5/2007
7/5/2007
8/5/2007
9/5/2007


View Replies!   View Related
Month And Day Only
I want to compare dates in two columns but I want to exclude the year.

For example:

column A 08/15/2002

Column B 08/21/2008

Column B - Column A = 6 days

View Replies!   View Related
Sum By Month ..
I want to use SUMIF to see if the range of cells are in the same MONTH as the criteria and sum a value in another column.

Something like this...

=SUMIF(MONTH( 'Data 2'!A:A),MONTH(Data!A2),'Data 2'!N:N)

This however throws up an error because of the MONTH tag round the first condition.

View Replies!   View Related
Last Day Of Month Bold
Col. A are dates,I would like to format Cells so the last day of the month is bold useing conditional fomating


View Replies!   View Related
Average By Month ...
I am trying to input monthly budgets based on a yearly budget for which I already have a few month's budgets. For example:

Jan - ?
Feb - ?
Mar - ?
Apr - ?
May - ?
June - $1105
July - $1325
Aug - $1470
Sept - $835
Oct - ?
Nov - ?
Dec - ?

TOTAL YEARLY BUDGET - $10,000

I need to find out what the other 8 months are, on average.

I'm looking for something along the lines of
each month's average IF SUM(A3:L3)=M3


View Replies!   View Related
Getting The Month Out Of The Date
I am working on a file that contains an install date. i'd like to create a new column that will only show the month per install date indicated. Can anybody help me create a macro for this?

View Replies!   View Related
First And Last Weekdays Of Month
I need a function that recognizes the first and last weekdays (M-F) of the month. I would prefer to do this with a function and not VBA.

View Replies!   View Related
Always Return 1st Of The Month In VBA
From the code below I need to translate whatever date is input to the First day of the month to pass into my VBA via the variable "SMth"

e.g. entered 12-03-09, returned 01-03-09

SMth = InputBox("Enter date of FIRST month ", "Format like 01-01-07", "01-01-07")
SMth = "=DATE(YEAR(SMth),MONTH(SMth),1)"
Cells(3, 8).Value = SMth
The line

SMth = "=DATE(YEAR(SMth),MONTH(SMth),1)"
is giving me an error, what should that line of code be?
OR perhaps you have another solution to reach the same goal


View Replies!   View Related
Sum Of Income By Month
I have a spreadsheet that contains entries for each order of a product and the product amount. What I want to do is have a summary of this for income. So, if there is a date completed for the order, I want a sum of this for the month.

Order No. Order Amount £ Date Ordered Date Complete
A2 B2 C2 D2


View Replies!   View Related
How Can I Find My 1st Of The Month
am working on a time sheet project. i'm now working on the monthly holiday planner.
Am looking for a way for a formlua to look at 2 rows and return the first of the month i.e 01-01-2009

The rows which need to be looked at are the first two rows of any given month because the 1st of the month will always be in the first two weeks of a month.
my rows are

1st week to look in. b4-h5
2nd week to look in. b19-h19

Now b4 as a paste link in it ..=roster!$H$2 which is the start date of the roster
the result of the search is then put into b2 as Month only ie. January

View Replies!   View Related
Workdays In A Month
I would like to know how to get the number of working days in a month based on the date in B4 which is formatted as "mmmm".
So if B4 was October the result would be 22 regardless of the actual date in B4.

I also have a named range "Holidays" for UK bank holidays (ready for December) that I would like included within the formula.


View Replies!   View Related
Month Of Year
As To Why This Is Giving The Answer Of "January Of 2009"?
For All Answers.
Sheet7


RS92/27/2009January Of 2009102/28/2009January Of 2009113/1/2009January Of 2009123/2/2009January Of 2009133/3/2009January Of 2009143/4/2009January Of 2009
Spreadsheet FormulasCellFormulaS10=TEXT(MONTH(R10),"MMMM")&" Of "&YEAR(R10)S11=TEXT(MONTH(R11),"MMMM")&" Of "&YEAR(R11)

Excel tables to the web >> Excel Jeanie HTML 4


View Replies!   View Related
If Month (), TRUE
I have two cells, each with month values in them.

In the first, C3, the full date is entered in format dd/mmm/yy. In the second, C5, the month is entered in full, say "November".

I want in C7, to have a formula that tells me when the month part of C3 is the same as either the month in C5, or any of the other quarter ends starting from that month.

So for example, if November is in C5, then my four months would be November, February, May, and August. If the month part of C3 (=MONTH(C3)) is equal to any of these months. I want to have to enter only one of the four months though!

Is there a formula that allows me to do this?

View Replies!   View Related
End Of Month Formula
I am calculating items that refer time service to days...The formula i am using now is
IF (ISBLANK (T2), TODAY (), T2) -IF (ISBLANK (I2), MAX(H2,S2), S2)

However i'm wondering what i can replace TODAY with to obtain a static date such as 12/31/08.

This formula/data is part of a macro that will be run by novice users each month end. So each month I want the measurable date to change. for example on Feb 1 I want the Macro to give me a date of 1/31/08, the following month 2/28/09.

Is there a way to correct the formula? or use a reference table?


View Replies!   View Related
List By Month
I want to be able to enter in a month and pull just the trainings for that month.

View Replies!   View Related
Last Day Of Current Month
I don't think there is a built-in function for retrieval of the last day of the month, is there?

Does anyone know how I can retrieve the last day of month using VBA?
So that I can use it like DATE.

View Replies!   View Related
Get Last Date For Month
I need a formual that will get the value that matches a month

In column A there are 52 weeks. I need to return the last non blank value for the month within the weekly period. The month value is found in column B

View Replies!   View Related
Month End Calculation
I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3.

My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month.

View Replies!   View Related
To Return A Certain Day Of A Month
I need a formula to look at a date manaully entered into a cell (C6 to be precise!), then return the 1st of that month. I.e if i type 18/01/07 into C6, i need C7 to automatically show the 1st of Jan 07 or 01/01/07. As this field will always be the 1st of the month.

View Replies!   View Related
Macro To Run On First Day Of Every Month
I need to run a macro on first day (1st) of every month at 07:00 am.

View Replies!   View Related
1st Monday In The Month
I am currently looking for a formula that will give me the actual date for the first Monday of the week.

I have for example in column A dates from 1st Jan 06 to 31st Jan 06 I just need to workout what the date is for the first Monday then after that for the 2nd Monday it would just be the 1st Monday +7.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved