Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
date of last friday of previous month
I got results with this formula in C1:
=B4-MOD(WEEKDAY(B4)+1,7) |
#6
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
4 and 5 week months | Excel Discussion (Misc queries) | |||
Pulling a date in the current month | Excel Worksheet Functions | |||
Formula for Extracting Month out of a Date column | Excel Discussion (Misc queries) | |||
Lookup the month in a date string 01/03/05 | Excel Worksheet Functions | |||
Roll back to previous date | Excel Worksheet Functions |