# Return Last Friday From A Given Month

May 23, 2007

Re: Return last Friday from a given month
This will calculate the last Friday of the month in which the date in A1 falls:

=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

HTH

TJ

actually this thread is closed. I want to convert this into UDF but not able.

## VBA Code To Return Previous Friday And Month Before Date

May 8, 2014

How to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.

For example...
today is may 8th

So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014

## Get Date Of Last Friday In Month?

Mar 25, 2012

If there's one thing I really wish I understood more in Excel its how to use formulas to calculate days and times.

I'm looking for a formula that will give me the date of the last Friday in a month based on another date. I have the formula below at the moment that works quite nicely for March 2012 but not for any others. It's getting the 5 at the end to be more flexible that's got me stumped.

=DATE( YEAR(H12), MONTH(H12) + 1, 0) - (WEEKDAY( DATE( YEAR(H12), MONTH(H12) + 1, 0), 2) - 5 )

## Determining Date Of 2nd Or Last Friday Of Any Month?

Sep 14, 2008

Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?

## Calculate 1st Tuesday Or 3rd Friday Of Month

Nov 11, 2009

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?

## First Monday, Thursday, And Friday Of Month

Jan 17, 2008

In cell A1 I have a date (like 8/1/08)

In A3, B3, and C3 I need to have the first Monday, Thursday, and Friday of the month. Here's the caveat, I need A3 to have the day that comes earliest in the month.

In this case, A3 would contain 8/1/08 since it's the first Friday, B3 would have 8/4/08 as the first Monday, then C3 would have 8/7/08 as the first Thrusday.

If A1 contained 7/1/08 then A3 would have 7/3/07 as the first Thursday with Friday and Monday following.

## Calculate Date Of Next Occurring 3rd Friday Of Month?

Oct 18, 2013

I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:

Code:
=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))

The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:

[URL]

## Formula Return Following Wednesday Or Friday Date

Jun 10, 2008

Need a formula for cell J27 that returns the date of the following Wednesday from a date in cell J2 that's a Wed, Thurs, or Fri
or
return the date of the following Friday for a date in cell J2 that's Sat, Sun, Mon, or Tue.

For example, when 05-29-08, a Thursday, is entered in cell F2, I need cell J27 to return the following Wednesday's date of 6-04-08.
or
For example, when 06-01-08, a Sunday, is entered in cell F2, I need cell J27 to return the following Friday's date of 06-06-08.

## To Return A Certain Day Of A Month

Mar 1, 2007

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.

## Always Return 1st Of The Month In VBA

Mar 20, 2009

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

## If Date Is Between 1st Through 1th Of Month Return Value

Jul 31, 2014

If I make a sale between the 1st of each month through the 1th of each month I will be paid on the 31st of that month. If I make a sale on the 16th of each month through to the last day of each month I will be paid on the 15th of the next month. I need this formula to work for every month.

cell e has date of sale
cell h has the returned value of the formula above

## Return Last Month (12-2009)

Jan 4, 2010

I have this code

Lastmonth = Format(Date, "mm-yyyy")

I would like to get it to returen the previous month. for example this month is 01-2010. I would like it to return 12-2009

## Return Date Of 3rd Day Of Following Month

Jun 27, 2012

I need to return the date of the third day of the month following the date in cell a1:

So, If A1= 06/01/12
A2 should = 07/03/12

## Return First Month That Has Data In It?

Feb 26, 2014

I am trying to come up with a formula that will give me the date in the cell right above the first cell that contains a value that is greater than zero.

Excel 2007ABCDEF11/1/20122/1/20123/1/20124/1/20125/1/20126/1/20122000501000Sheet2

In this example I would want to return 4/1/2012

## Return Date Of Specified Day In Month

Sep 13, 2007

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...

## Return Date Of Last Day Of Given Month

Oct 16, 2007

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.

## Return Month Based On Day Of The Year?

Mar 27, 2014

I 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.

## Return Value That Corresponds To Latest Day In Month?

Oct 11, 2012

I am in need of a vlookup formula that will return a value that corresponds to the latest day in a month. Example:

Column A had dates in format 1/3/2012 ect....
Column B is a \$ amount
Column C is a date in format Oct-12 ect...

I want column D to be a formula that match up column C's month in column A and return the \$ in column B that corresponds to the latest day in the month. IN the example above it would returna value for Oct 31 (if there was one), Oct 30 next and then Oct 29 ect...

So if column A had October dates of: 10/1, 10/4, 10/17 and thats it in Oct then it would show the 10/17 \$ in column B.

## Return The Month Integer From A Date

Aug 5, 2008

Upon trying to use the Month function (to return the month integer from a date), in excell 2007, I get the

"Expected variable or procedure, not module

followed by (if I drill down into the help file)

There is no variable or procedure by this name in the current scope, but there is a module by this name"

error message

Do I assume there is a way around this (or if I have to do something to 'activate' the month function)

## Return Number Of Days In A Month From Dataset

May 7, 2014

I have some data & want to count the individual number of days for each month.

Example attached..

## Search For Payments Made In A Certain Month And Return The Sum

Sep 14, 2009

How can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.

## Enter A Date And Return The Fiscal Month

Dec 15, 2008

Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?

Start End Month
09/21/08 10/18/08 Oct
10/19/08 11/22/08 Nov
11/23/08 12/27/08 Dec
12/28/08 01/24/09 Jan
01/25/09 02/21/09 Feb
02/22/09 03/28/09 Mar
03/29/09 04/25/09 Apr
04/26/09 05/23/09 May
05/24/09 06/27/09 Jun
06/28/09 07/25/09 Jul
07/26/09 08/22/09 Aug
08/23/09 09/26/09 Sep

## Return Current Month Using Excel Formula?

Feb 22, 2014

get the current month name in a Cell A1 and Next month name in Cell B1. what formula should I used? This will be a part of the macro that I'm creating.

The macro will be use every 11th to the last day of the month so I want the formula to be dynamic enough to work in each day the macro will be use.

Expected result using the current Date Today

A1 B1
February March

## Return A Date Based On Year / Month

Jan 21, 2009

formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.

Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...

is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?

The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.

## A Forumla To Look At A Year And Month And Return A 1 If Match Found?

Apr 3, 2014

In cell b2 I have a formula =text(today(),"yyyy") which daisplays todays date as a year, in cell b3 I have =text(today(),"mmmm") which displays todays date current month.

I have a column of Months and Years

January 2014
February 2014 etc

I need todays (current) month and year to indicate in a helper column next to the relevent month and year as a number 1 to use as a reference to return date from the row where the 1 is displayed, I have tried =if(and(b2=a10,b3=b10),1,"")) and it dosent like the fact that the year in the colmuns is entered as standard text, is there any whay to do this?

## Formula To Return Average Days Between Two Dates In A Given Month?

Jan 13, 2014

I have a workbook with two sheets. The first has a list of job positions open, columns designated to stages in the employment process and in these columns, my staff enter the dates that they completed a particular stage.

"Position | Date Opened | Stage 1 | Stage 2 | Stage 3 | Stage 4"
Pos 1 | 01/01/2014 |02/01/14|03/01/14|07/01/14|09/01/14

This has a great number of entries and they are increasing and decreasing every day depening on the amount of jobs available.

On the second sheet, I am trying to set up a table which shows the average working days it is taking to complete each stage, divided into the months in which the job position was opened (i.e. for positions opened in january, the average completion working days for stage 1 was X amount of days etc...)

I have tried using =IF(AND(Logical, Logical),TRUE,FALSE) but this doesn't return any info as the logicals will always be false. I tried the OR function, but that requires only one of the criteria to be true to return a value. What I need is for the formula to return a number of days between two dates, ONLY if the opened date falls in one month.

## Excel 2003 :: Getting Formula Which Will Return Each Date Of Month

Sep 10, 2013

I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.

## Return Month Number Of Date Unless Cell Empty

Apr 9, 2008

I am using the MONTH function in a formula. The Month value of an empty cell is returned as "1". Why is this? Is there any way to return a null value or zero?

## Using COUNTIF To Return The Sum Of Dates That Fall Within A Month/Year Range..

Jan 20, 2010

I want to use a COUNTIF to return the sum of all the dates that fall within a given month/year. For example: E1 Contains the date July-2009

Column A has date entries such as July 3, 2009, July 18, 2009, August 4 2009. In F1 I want to return the sum of all dates that fall within the month of E1.

## Search Range For Current Month & Year Return Corresponding Values

Nov 21, 2007

I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.