| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can i set a formula which shows 2nd last Monday of the month?
One mo-
1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can i set a formula which shows 2nd last Monday of the month?
Try with date in A1
In B1 =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5 If this post helps click Yes --------------- Jacob Skaria "J" wrote: One mo- 1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can i set a formula which shows 2nd last Monday of the mon
yes. it works
thx -- J "Jacob Skaria" wrote: Try with date in A1 In B1 =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5 If this post helps click Yes --------------- Jacob Skaria "J" wrote: One mo- 1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can i set a formula which shows 2nd last Monday of the mon
There is a small correction to the formula..To see the error try a date in A1
of May2009 with the previous formula and the current. =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)=1,12,5) If this post helps click Yes --------------- Jacob Skaria "J" wrote: yes. it works thx -- J "Jacob Skaria" wrote: Try with date in A1 In B1 =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5 If this post helps click Yes --------------- Jacob Skaria "J" wrote: One mo- 1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can i set a formula which shows 2nd last Monday of the mon
On Tue, 30 Jun 2009 03:46:02 -0700, J wrote:
yes. it works thx -- J "Jacob Skaria" wrote: Try with date in A1 In B1 =(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5 If this post helps click Yes --------------- Jacob Skaria "J" wrote: One mo- 1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J There seem to be a number of instances for which this formula returns the Last Monday, and not the second to last Monday. For example: 10-May-2009 -- 25-May-2009 January 2010 also -- Last Monday Feb 2010 Try this instead: =DATE(YEAR(A1),MONTH(A1)+1,-6)- WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)-1) --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can i set a formula which shows 2nd last Monday of the month?
=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),3)-7
"J" wrote: One mo- 1. How can i set a formula which shows 2nd last Monday of the month? A1 = 2009/07/10 A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month) OR A1 = 2009/07/22 A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month) -- J |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find date of first Monday of each month | Excel Discussion (Misc queries) | |||
| Date calculation for Monday of one month to the Monday of the next | Excel Discussion (Misc queries) | |||
| Calculating first/last Monday, Tuesday, etc. in a given month in E | Excel Worksheet Functions | |||
| 1st Monday of a month in date range?? | Excel Worksheet Functions | |||
| Finding the date on the 'nth' Monday in this Month in this Year | Excel Worksheet Functions | |||
Linear Mode
