Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the
Sunday dates of that month to automatically enter in non-adjacent cells on the form. I've searched and cannot get this to work - help would be appreciated!! thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
With 12/1 entered into cell A1, use
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) for the first Sunday, then for the next Sundays, use =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 7 =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 14 =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 21 and if there is a fifth Sunday: ==IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKD AY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 28 )=MONTH(A1),DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEK DAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 28,"") HTH, Bernie MS Excel MVP "rejoyce40" wrote in message ... I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the Sunday dates of that month to automatically enter in non-adjacent cells on the form. I've searched and cannot get this to work - help would be appreciated!! thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
Try these:
A1 = some date A3 = 1st Sunday A5 = 2nd Sunday A7 = 3rd Sunday A9 = 4th Sunday A11 = 5th Sunday (if there is one) Enter these formulas in their respective cells: A3: for the 1st Sunday =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1) A5: for the 2nd Sunday =A3+7 A7: for the 3rd Sunday =A3+14 A9: for the 4th Sunday =A3+21 A11: for the 5th Sunday (if ther is one) =IF(MONTH(A9+7)=MONTH(A1),A9+7,"") You might have to format the cells as Date -- Biff Microsoft Excel MVP "rejoyce40" wrote in message ... I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the Sunday dates of that month to automatically enter in non-adjacent cells on the form. I've searched and cannot get this to work - help would be appreciated!! thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
Non-adjacent?? How?
Regards, Bernd |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
Wow, that worked just exactly like I wanted - THANK YOU, THANK YOU, THANK
YOU!!! "T. Valko" wrote: Try these: A1 = some date A3 = 1st Sunday A5 = 2nd Sunday A7 = 3rd Sunday A9 = 4th Sunday A11 = 5th Sunday (if there is one) Enter these formulas in their respective cells: A3: for the 1st Sunday =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1) A5: for the 2nd Sunday =A3+7 A7: for the 3rd Sunday =A3+14 A9: for the 4th Sunday =A3+21 A11: for the 5th Sunday (if ther is one) =IF(MONTH(A9+7)=MONTH(A1),A9+7,"") You might have to format the cells as Date -- Biff Microsoft Excel MVP "rejoyce40" wrote in message ... I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the Sunday dates of that month to automatically enter in non-adjacent cells on the form. I've searched and cannot get this to work - help would be appreciated!! thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates of month
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "rejoyce40" wrote in message ... Wow, that worked just exactly like I wanted - THANK YOU, THANK YOU, THANK YOU!!! "T. Valko" wrote: Try these: A1 = some date A3 = 1st Sunday A5 = 2nd Sunday A7 = 3rd Sunday A9 = 4th Sunday A11 = 5th Sunday (if there is one) Enter these formulas in their respective cells: A3: for the 1st Sunday =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1) A5: for the 2nd Sunday =A3+7 A7: for the 3rd Sunday =A3+14 A9: for the 4th Sunday =A3+21 A11: for the 5th Sunday (if ther is one) =IF(MONTH(A9+7)=MONTH(A1),A9+7,"") You might have to format the cells as Date -- Biff Microsoft Excel MVP "rejoyce40" wrote in message ... I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the Sunday dates of that month to automatically enter in non-adjacent cells on the form. I've searched and cannot get this to work - help would be appreciated!! thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert dates to month | Excel Worksheet Functions | |||
dates, 1 month prior | Excel Worksheet Functions | |||
Dates - Several Days In a month to month only | Excel Discussion (Misc queries) | |||
dates in one month | Excel Worksheet Functions | |||
dates, 1st of month | Excel Worksheet Functions |