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 )

View 2 Replies


ADVERTISEMENT

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?

View 14 Replies View Related

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]

View 4 Replies View Related

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

View 6 Replies View Related

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.

View 8 Replies View Related

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?

View 3 Replies View Related

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.

View 9 Replies View Related

Move Date To Next Monday If Date Is On Friday / Saturday Or Sunday

Aug 24, 2014

I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.

View 3 Replies View Related

Find Pay Date From Date Sold (Friday Of Following Week)

Feb 17, 2014

If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.

View 3 Replies View Related

How To Get Last Friday Date Based On Current Date

Feb 14, 2013

I need to work out how to get the last (or most recent) Friday date. Suppose today is 14/02/2013, then the previous date is: 08/02/2013. If today is 15/02/2013, then I just want it to be 15/02/2013 as it is Friday.

View 3 Replies View Related

Returning The Date For Last Friday?

Feb 12, 2008

how to write an excel workbook function that will return last friday's date?

For example: If Today is 2/12/2008, it will return 2/8/2008 as the date

View 6 Replies View Related

Calculating Friday Date

Aug 7, 2006

I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.

View 9 Replies View Related

Generate A List With The Date Of Every Other Friday

May 30, 2009

On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:

Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................

What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

View 10 Replies View Related

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.

View 9 Replies View Related

Change Cell Color If Date Equals To Friday

Mar 18, 2009

is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?

Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar

View 4 Replies View Related

Excel 2007 :: Count Number Of Days Between Two Date Where Off Days Are Friday / Saturday And Holiday

Mar 14, 2014

I am making process TAT(Turn Around Time) which required following information. In Excel 2007.

1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays

2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays

Note : Any weekend (off days) dates listed in holidays should not effect the query.

View 9 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

Jan 28, 2010

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 9 Replies View Related

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)

Mar 20, 2009

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 9 Replies View Related

Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)

Jul 28, 2008

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 9 Replies View Related

Reflect Month Name When Column A Date Is Same Day And Month

Jan 8, 2014

How to correct this formula to get correct output.

=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))

It's working fine if the year is 2014 but not if the year is different.

Conditions:

If the date is today then only the month should display else blank. The year should be ignore.

For example:

08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell

The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.

View 10 Replies View Related

Month(Date): If The Month Is Not January It Works

Aug 24, 2009

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 3 Replies View Related

Calculate Number Of Days Between Two Date Within A Current Month Including End Date

Apr 2, 2009

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

View 9 Replies View Related

VBA - Validate Date Entered In Input Box Is Month Ahead Of Date In Cell

Aug 14, 2012

I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.

It is a monthly reset so it has to roll on from the previous month.

Here is what i have currently but it isn't working.

Code:

' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then

[Code]...

View 1 Replies View Related

How To Calculate Average Of Values Against Selected Date Of Each Month Within Date Range

Jan 2, 2014

I have a table that looks like this (its basically a historical data of a stock exchange):

Date
Index
January 4, 2010

[Code]....

The List continues till the current Date.

I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.

Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.

View 1 Replies View Related

Year Month Date To Month Date Year Code

Jul 28, 2009

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 9 Replies View Related

Weekday/workday Date 1 Month After The Last Contact Date

Sep 20, 2009

I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.

I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.

So, if the date is on a weekend, I need the Monday date instead.

View 5 Replies View Related

Count Date Cells Where Date Is Previous Month

Oct 25, 2006

I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.

My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.

View 9 Replies View Related

Check Whether Date Is A Month After Previous Date?

Dec 12, 2013

I'm sent a file every day where column A contains a month & year but in "general" format.

For example;

Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15

Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.

View 3 Replies View Related

Date To Show Only Date Without Month Or Year

Jan 9, 2013

I have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved