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:
Calculate 1st Tuesday Or 3rd Friday Of Month
I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

View Replies!   View Related
Sum By Specific 1st Day Of Month
I have 365 days of data that I need to sum by specific weekday. I need to Sum every fisrt Sundays of each month for the year, then each mondays, Tuesdays, etc. I looked all over the forum and was not able to find a thread. Is ithat possible to do?

View Replies!   View Related
Macro Or Formula That Will Sum On 1st Day Of The MOnth
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following

View Replies!   View Related
Lookup Results For 1st & Last Day Of Month
I have a series of daily data in columns sorted by date (see attached .xls).

I am trying to create a table that extracts the value on the last day of a given month, and of the first day of that month, for each of the months in the data series.

I have been trying to get lookups and match functions to work but to no avail.

View Replies!   View Related
Format A Number As 1st, 2nd, 3rd Etc.
I cannot figure this out, but does anyone know the custom format code to do a number into 1st, 2nd, 3rd, 4th, 5th, etc.

View Replies!   View Related
Find 1st, 2nd And 3rd Place...?
We're doing a "Biggest Loser" competition at work. I have a spreadsheet that calculates the % of body weight lost. I need a formula that will look at the results and display 1st, 2nd, 3rd places. Here is this weeks results...

View Replies!   View Related
Take The First Letter From The 1st, 2nd, 3rd Names Of The List
I have a few names in the range A2:A11 and the exercise asks: Fill in the cell B19 with a temporary software name. Using the appropriate function, compose such name as follow: take the first letter from the 1st, 2nd, 3rd names of the list; take the third letter from the 4th, 5th, 6th names of the list; take the last letter from the 7th, 8th, 9th, 10th names of the list. Moreover, the software name must be in capital letters.

View Replies!   View Related
Locate & Return The 1st Or 2nd Or 3rd Value
I have one column of data (numbers). Within this column, are the values for two different categories...which are separated by a blank row. And then a total for each product given one column over; like this:

Product1 123
Product1 456
Product1 789 1368

Product2 456
Product2 123
Product2 456 1035

Either product can increase/decrease in rows, so I am unable to set a static range. The only solution I got on my own was to use SUMIF (sum the column of values if it matches the name "Product1" or "Product2"). This is fine until someone misspells the product name or leaves it off completely; which is why I prefer to go off the actual values.

My 1st preference is to go off the totals. Basically, return the 1st value (ie 1368) in the column, and then in another cell, return the 2nd value in the column (ie 1035).

My 2nd preference would be to sum the individual values in the column until a blank cell is encountered, and then return that value. Then continue on until another blank cell is encountered, and return that value as well.

I know very little of vba, so I haven't taken that route, but I bet this is easily done as a UDF (although I would prefer not to do it that way).

View Replies!   View Related
Day Of Week -day Of Month List Auto Setup
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)

View Replies!   View Related
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Month Day Day Of Week
1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week

=$D$1
=$D$1+E5-1
=TEXT(WEEKDAY(D6),"ddd")

cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.

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
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
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
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
Change Dates To The 1st Of The Month
I am working in a large database and trying to change the date field. I want to change all the dates to the first of the month.(ie 10/19/2008 to 10/01/2008) Is there a way I can change this in excel without having to go in and manually change all the days to the first.

View Replies!   View Related
1st Monday Of A Month In Date Range
to determine the number of 1st Mondays or any weeday e.g. 5th Fridays between a date range e.g. 1/1/2006 - 4/1/2006.

I've noticed a lot of threads for calculating just one month but none for a period of time.

View Replies!   View Related
Automatically Open A New Worksheet Every 1st Of The Month
I have been trying for the past four months now to get my workbook to automatically open a new worksheet every 1st of the month.

but I have failed each and every month. I have asked others to assist but to no avail.

this is the code in my workbook as it looks now.

Dim strValue As String
Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String
Dim rng As Range
Dim irow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Dim strName As String
Dim wsh As Worksheet

i keep getting an error on the activesheet=newsheet.

View Replies!   View Related
Add Income Only Between 1st And 15th Of Each Month
I am a car salesman and made a sheet to track my sales from month to month. I would like to make a formula to only add the entries that fall between the 1st and 15th of each month to show me what my check will be for the first half of the month. Column A has the dates and column J has the amount. I need to calculate only the first half of the month in J24 to know what my mid month check will be......

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 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
Matching 2 Fields In 2 Columns And Copying A 3rd Field Into The 3rd Column
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.

For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").

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
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
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
Last Busniess Day In Each Month
I am trying to find an easy way to calculate the last business day in each month? Can I use the workday function somehow with eomonth? Not sure b/c it is variable depending on if the month ends on a weekend or not.

View Replies!   View Related
Name Of Day/Month Parameter
Looking for a formula that gives the 1st - 5th day of a month, using the day name, e.g. "First Monday of March 2009" returns "2". Even better is if it could accomodate previous and following months, e.g. "First Monday of Next Month" returns "6". 3

View Replies!   View Related
Last Day Of Previous Month
I have an excel document which I am trying to automate using VBA. I have two headings which have the same text every month but the month value must change.

Basically I’m looking for the last day of the previous month. So if it was 05th April 2007 I want to return 28 March 2007. I just need to obtain the last day of the previous month in the following format ’28 March 2007’.

View Replies!   View Related
Formula: Day Into Month+year
i need to put a date into a month +year
like this:
1/06/2008jun/08

i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.

MaandTotal
jun/081jun/0810

he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun

but i just want one and that he counts all the junes together


View Replies!   View Related
Auto-calculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).


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
Last Working Day Of Month Formula
I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.

This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.

A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....

View Replies!   View Related
Obtaining Birthday Day From Next Month.
I use the following fomula to test and advise of an upcoming birthday:-

=IF(AND(MONTH(TODAY())-MONTH(B6)=0,DAY(B6)-DAY(TODAY()) 25.


View Replies!   View Related
Formula To List The First And The Last Day Of Each Month
is there a formula to list the first and the last day of each month and for every year?.

example:
if i put in cell A1: 01/09/06 ----->01/September/2006

the result should be:
A2: 30/09/06 ------> 30/September/2006
A3: 01/10/06
A4: 31/10/06
A5: 01/11/06
A6: 30/11/06
A7: 01/12/06
A8: 31/12/06
A9: 01/01/07
A10: 31/01/07
A11: 01/02/07
A12: 28/02/07
.
.
.
and so on

how to do that with a formula?

View Replies!   View Related
Ignore Day And Only Refer To The The Month?
The summary page summarises the source data into how much revenue each department has made over 12 months.

However the formula in Summary worksheet column B will only total up the amounts if the month in the Source worksheet column B is the 1st of the month. How do I change the formula in Summary worksheet column B to purely go by month and ignore the day?

View Replies!   View Related
Cell With Day Of Month ONLY (and Volatile)
I have searched around the forum but I could not find anything related. Like the title says, I need to have a cell that is the current day of the month and changes automatically each day.

View Replies!   View Related
Return Date Of Specified Day In Month
Is there a end of "week" function which shows only the date the friday? says ..if today is 9/13/2007, the cell will show 9/7/2007...

View Replies!   View Related
Return Date Of Last Day Of Given Month
I'm after a formula(s) which will give the start date and end date of a month using the month name. For instance, if cell A1 contained OCTOBER 07 the formula result for the start of the month would be 01/10/07 and the formula for the end of the month would be 31/10/07. I have plyed around with the EOMONTH function but this only workd when an actual date is entered.

View Replies!   View Related
Show Nth Day Of Month
I have a daily history of the S&P that I downloaded from Yahoo Finance. I would like to show only the third friday of the month and hide the rest of the days.

View Replies!   View Related
Formula To Display The Last Day Of The Previous Month
I am using the following formula to show the first day of previous 6 month cycle.

=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)

That formula would display: 07/01/2009

I want to have another formula show the last day of the previous month.

Basically it would display: 12/31/2009

View Replies!   View Related
Reverse Date (month & Day)
I have the following date problem, for example I need to reverse the following date:

8/02/2003 (which is recognised as a date) to:

2/08/2003 (the real date).

Is there an easy way to do this using a formula?

View Replies!   View Related
Report Of Table By Day & Month
I am using Excel 2007 and need some help. If you could please download and open the attached excel sheet then i guess you will be able to understand my problem easily. I am also not sure if what i am asking is really possible or not in Excel (would prefer without any VBA coding but if there's no other way then VBA coding would also do).

What i need is that based on the data from 'data' sheet i want to dynamically fill the ' Date' column in 'CatA' sheet. If you check the 'Expected Result' sheet where i have manually filled the dates and automatically calculating the 'Amount' but i was curious to know if somehow i could dynamically fill the values in 'Date' column based on month name in header?

View Replies!   View Related
CSV File To Save For Each Day Of The Month
I have searched the forum regarding copying data from multiple worksheets into a main workbook, but I did not find anything that tells me how to copy data from multiple csv files into one workbook tab, I can see a thread created by Hiport which is very similar to what I want.

I have a CSV file saved for each day of the month, these files are saved in their monthly folders, so all the CSV files for May will be saved in U:CustodiansInterestYearMonth(May2008), now in this folder will also be a master workbook called “Interest-May2008”.
What I do next is very manual, I have to copy from each CSV file, data from Col 1 and Col2 and paste this to the master workbook- sheet (downloads), all the data will be pasted next to each other, i.e. col 1 and 2 will be data for 01-May-08, and col 3 and 4 will have data for 02-May-08 etc, as you can see it’s a very daunting task having to go through each file and copy and paste.
I need a macro written so it copies data in col 1 and 2 in each csv file and then pastes that data in a master workbook sheet (downloads), the data will be in date order of the CSV files, I want the data pasted in row 3 of the master workbook (downloads).

View Replies!   View Related
Date Display Like Day/Month/Year
Im having a little issue with the way the dates are layed out.

I have a condition format =AND(RC5="No",TODAY()<RC4+10)

And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works.

Though i have always written dates like Day/Month/Year.
ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.

View Replies!   View Related
Add Leading Zero To Month And Day After Split
I'm trying to finish up a little program, but am stuck on getting date values from excel over to a another front end system correctly. The issue is pretty basic - I have a value I call "fulldate" which, for example is "1/5/2010"

I have to move each element of the date to this other system, but if I don't add the 0 before the month and day, it gets pushed over incorrectly (this other front end has three fields which accept: mm dd yyyy. BUT, if one character is entred into the mm field, you need to TAB to get to the dd field. If you enter TWO characters, you CANNOT tab to get there (you'd end up in the yyyy field).

So, all I really need to do is take the value "1/5/2010" and end up with three variables of consistent lengths.

Month = 2 characters
Day = 2 characters
Year = 2 characters

Here's what I'm trying tu use.. .but Day1 = 1, not 01. I've tried using format, but it's just not working how I'd expect.

View Replies!   View Related
Converting A Day Date To A Month And Year
I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.

View Replies!   View Related
If Month And Day Not In Cell Delete Row
in column A i have the dates,

if the month and day of a cell is not equal to a keeper i'd like to delete that row

i found some code in the forum and mangled it a bit but it does not work right

Sub xyz()
range("A1").Select
Let keeper1 = 1
Let keeper2 = 4
Do Until Month(ActiveCell.Value) <> keeper1 And Day(ActiveCell.Value) <> keeper2
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Delete

Loop

End Sub

it deletes 1 cell but does not loop like its supposed to and find all the non-keepers :P

View Replies!   View Related
Last Business Day Of Prior/Previous Month
what formula should I use to refer to the last working day on the prioir month. I have trie to look up forums but no result os yet.

View Replies!   View Related
Locate Last Day Of Month Excluding Weekends
I have a spreadsheet with bloomberg's auto feed, so in col A&B, the price will be updated everyday. I would like to calculate the Month-to- Date in Col C

e.g. MTD for today's closing = (today's closing price - last month end price)/last month end price

how can I constructure a formular in col C which will calculate the MTD automatically when a new price is added to the top everyday.

View Replies!   View Related
VBA Message Box For First Business Day Of The Month
I am looking for vba code that will create a message box on the first business day of every month as a reminder.

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