Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
This is probably a very basic question so I apologise first, how would I
calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:
This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks With the month, e.g. 2008-03 in cell A1try the following formula: =DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)) The following Friday can be obtained by just adding 5 to the above Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
=DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0))
=DATE(YEAR(A1),MONTH(A1)+1,6)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)) -- David Biddulph "Dale" wrote in message ... This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
or
=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),1,7,6,5,4,3,2) "Dale" wrote: This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
all of which has got nothing to do with the question set by the OP!! who
wanted the last Sunday and not the first Monday!! "Mike H" wrote: or =A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),1,7,6,5,4,3,2) "Dale" wrote: This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
Thanks.....that's what I was looking for
"Lars-Åke Aspelin" wrote in message ... On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote: This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks With the month, e.g. 2008-03 in cell A1try the following formula: =DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)) The following Friday can be obtained by just adding 5 to the above Hope this helps / Lars-Åke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)-1,7)
=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)-1,7)+5 "Dale" wrote: This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sunday
On Sun, 30 Mar 2008 12:04:43 +0100, "Dale" wrote:
This is probably a very basic question so I apologise first, how would I calculate the last sunday of a given month? Once I have that date, I need the following Friday...... Thanks If you have Excel 2007, or in an earlier version, if you have the Analysis ToolPak installed, with any date in A1,you can use: Sunday =EOMONTH(A1,0)+1-WEEKDAY(EOMONTH(A1,0)) For the following Friday, merely add 5 to the above =EOMONTH(A1,0)+6-WEEKDAY(EOMONTH(A1,0)) If you get a #NAME error, check HELP for EOMONTH and it will tell you what to do. --ron |
#9
|
|||
|
|||
Quote:
Sorry for the "delay". The following formula seens to be the shortest: =INT((EOMONTH(A1,0)-1)/7)*7+1-2 Michael Avidan “Microsoft®” MVP – Excel ISRAEL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last Sunday | Excel Worksheet Functions | |||
How do I set up 16 team fixtures each Sunday | Excel Worksheet Functions | |||
Skipping Saturdays and Sunday | Charts and Charting in Excel | |||
Sunday Function | Excel Worksheet Functions | |||
Help with looking the nearest Sunday | Excel Worksheet Functions |