Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tkaplan
 
Posts: n/a
Default date of last friday of previous month


I am trying to figure out how to compute the following:

user enters in a date in A1.
B1 needs to calculate the month prior.
C1 needs to calculate the last friday of the month prior to B1.

so if A1 is 11/14/2005,
B1 should be October 2005
C1 should be 9/30/2005

I know how to do B1. I am having a hard time with C1.

Thanks in advance


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=484884

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default date of last friday of previous month

On Mon, 14 Nov 2005 09:52:36 -0600, tkaplan
wrote:


I am trying to figure out how to compute the following:

user enters in a date in A1.
B1 needs to calculate the month prior.
C1 needs to calculate the last friday of the month prior to B1.

so if A1 is 11/14/2005,
B1 should be October 2005
C1 should be 9/30/2005

I know how to do B1. I am having a hard time with C1.

Thanks in advance


One problem -- 30 Oct 2005 was a Sunday. Do you want the last Sunday of the
prior month or the last Friday.

B1: =A1-DAY(A1)
Format as: mmmm yyyy

C1: =B1+1-WEEKDAY(B1+2)
will give the last Friday of the month prior to the month in A1

If you want the last Sunday of the month, then:

C1: =B1+1-WEEKDAY(B1)


--ron
  #3   Report Post  
tkaplan
 
Posts: n/a
Default date of last friday of previous month


i need the last friday of the month prior to the month in b1.

september 30 was a friday. if a1 is 11/14/05, i need b1 to say October
2005, and c1 to say 9/30/2005.
b1 i know how to do, i am having a problem with C1.


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=484884

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default date of last friday of previous month

On Mon, 14 Nov 2005 09:52:36 -0600, tkaplan
wrote:


I am trying to figure out how to compute the following:

user enters in a date in A1.
B1 needs to calculate the month prior.
C1 needs to calculate the last friday of the month prior to B1.

so if A1 is 11/14/2005,
B1 should be October 2005
C1 should be 9/30/2005

I know how to do B1. I am having a hard time with C1.

Thanks in advance



My misreading. Try:

B1: =A1-DAY(A1)

C1: =B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+2)


--ron
  #5   Report Post  
Dave O
 
Posts: n/a
Default date of last friday of previous month

I got results with this formula in C1:
=B4-MOD(WEEKDAY(B4)+1,7)



  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default date of last friday of previous month

On 14 Nov 2005 08:50:47 -0800, "Dave O" wrote:

I got results with this formula in C1:
=B4-MOD(WEEKDAY(B4)+1,7)


1. You misread the same as I did. The OP wants the last Friday of the month
PRIOR to the month in *B4*.

2. The MOD function seems superfluous as WEEKDAY effectively performs a MOD
function by itself.

=B4+1-WEEKDAY(B4+2)

will return the same value as your formula.


--ron
  #7   Report Post  
tkaplan
 
Posts: n/a
Default date of last friday of previous month


thank you ron. that worked:)


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=484884

  #8   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default date of last friday of previous month

On Mon, 14 Nov 2005 11:39:16 -0600, tkaplan
wrote:


thank you ron. that worked:)


You're welcome. Glad to help.


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
4 and 5 week months Big Rick Excel Discussion (Misc queries) 15 November 7th 05 01:32 AM
Pulling a date in the current month Brian Excel Worksheet Functions 2 October 24th 05 05:57 PM
Formula for Extracting Month out of a Date column PokerZan Excel Discussion (Misc queries) 3 June 10th 05 08:30 PM
Lookup the month in a date string 01/03/05 Una Excel Worksheet Functions 1 March 30th 05 09:45 AM
Roll back to previous date Jay Excel Worksheet Functions 2 December 3rd 04 04:35 PM


All times are GMT +1. The time now is 12:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"