| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find date of last weekday of a month
"TUNGANA KURMA RAJU" skrev i en
meddelelse ... I need a function/formula to get date of last friday of a month. if cell A1=10-OCT-2006 --- RESULT IN cell B1=27-OCT-2006 if cell A1=01-SEP-2006 ----RESULT IN CELL B1=29-sep-2006. Another option: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+1,7)+1) The generic formula is: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1) If Sunday = weekday number 1: X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday, Thursday, Friday, Saturday or put another way: X = 7 - weekday number -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find date of last weekday of a month
On Sat, 28 Oct 2006 13:42:37 +0200, "Leo Heuser"
wrote: "TUNGANA KURMA RAJU" skrev i en meddelelse ... I need a function/formula to get date of last friday of a month. if cell A1=10-OCT-2006 --- RESULT IN cell B1=27-OCT-2006 if cell A1=01-SEP-2006 ----RESULT IN CELL B1=29-sep-2006. Another option: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+1,7)+1) The generic formula is: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1) If Sunday = weekday number 1: X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday, Thursday, Friday, Saturday or put another way: X = 7 - weekday number I think you can eliminate the MOD functions as WEEKDAY already effectively does a MOD 7 (MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+1,7)+1) = WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))+1 --ron |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find date of last weekday of a month
"Ron Rosenfeld" skrev i en meddelelse
... On Sat, 28 Oct 2006 13:42:37 +0200, "Leo Heuser" wrote: "TUNGANA KURMA RAJU" skrev i en meddelelse ... I need a function/formula to get date of last friday of a month. if cell A1=10-OCT-2006 --- RESULT IN cell B1=27-OCT-2006 if cell A1=01-SEP-2006 ----RESULT IN CELL B1=29-sep-2006. Another option: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+1,7)+1) The generic formula is: =DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1) If Sunday = weekday number 1: X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday, Thursday, Friday, Saturday or put another way: X = 7 - weekday number I think you can eliminate the MOD functions as WEEKDAY already effectively does a MOD 7 (MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+1,7)+1) = WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))+1 --ron You are right. Thanks, Ron! Leo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| holiday dates | Excel Worksheet Functions | |||
| Find category value based on date range? | Excel Worksheet Functions | |||
| average value from a table | Excel Discussion (Misc queries) | |||
| Get month from date | Excel Discussion (Misc queries) | |||
| Pulling a date in the current month | Excel Worksheet Functions | |||
Hybrid Mode
