| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate days of the month in a column by checking on date
Is it possible to populate a column with the days of any given month by
checking on a cell reference to a date? For example if A1 contained 2/28/2009 is there a way for the system to populate B1 through B28 with the dates of the month? Then if you changed A1 to 3/31/2009 have it populate B1 through B31 with the dates for March? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populate days of the month in a column by checking on date
Try this...
A1 = any date Enter this formula in A2: =A1-DAY(A1)+1 Enter this formula in A3 and copy down to A32: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Format A2:A32 as Date If A1 is empty the dates returned will be for January 1900. -- Biff Microsoft Excel MVP "Joe" wrote in message ... Is it possible to populate a column with the days of any given month by checking on a cell reference to a date? For example if A1 contained 2/28/2009 is there a way for the system to populate B1 through B28 with the dates of the month? Then if you changed A1 to 3/31/2009 have it populate B1 through B31 with the dates for March? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populate days of the month in a column by checking on date
Ooops!
I put the formulas in the wrong locations! You wanted the results in B1:B31. Ok. Enter this formula in B1: =A1-DAY(A1)+1 Enter this formula in B2 and copy down to B31: =IF(DAY(IF(B1="",0,B1)+1)DAY(B$1),B1+1,"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... A1 = any date Enter this formula in A2: =A1-DAY(A1)+1 Enter this formula in A3 and copy down to A32: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Format A2:A32 as Date If A1 is empty the dates returned will be for January 1900. -- Biff Microsoft Excel MVP "Joe" wrote in message ... Is it possible to populate a column with the days of any given month by checking on a cell reference to a date? For example if A1 contained 2/28/2009 is there a way for the system to populate B1 through B28 with the dates of the month? Then if you changed A1 to 3/31/2009 have it populate B1 through B31 with the dates for March? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populate days of the month in a column by checking on date
Now that is really cool!
"T. Valko" wrote: Ooops! I put the formulas in the wrong locations! You wanted the results in B1:B31. Ok. Enter this formula in B1: =A1-DAY(A1)+1 Enter this formula in B2 and copy down to B31: =IF(DAY(IF(B1="",0,B1)+1)DAY(B$1),B1+1,"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... A1 = any date Enter this formula in A2: =A1-DAY(A1)+1 Enter this formula in A3 and copy down to A32: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Format A2:A32 as Date If A1 is empty the dates returned will be for January 1900. -- Biff Microsoft Excel MVP "Joe" wrote in message ... Is it possible to populate a column with the days of any given month by checking on a cell reference to a date? For example if A1 contained 2/28/2009 is there a way for the system to populate B1 through B28 with the dates of the month? Then if you changed A1 to 3/31/2009 have it populate B1 through B31 with the dates for March? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populate days of the month in a column by checking on date
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Joe" wrote in message ... Now that is really cool! "T. Valko" wrote: Ooops! I put the formulas in the wrong locations! You wanted the results in B1:B31. Ok. Enter this formula in B1: =A1-DAY(A1)+1 Enter this formula in B2 and copy down to B31: =IF(DAY(IF(B1="",0,B1)+1)DAY(B$1),B1+1,"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... A1 = any date Enter this formula in A2: =A1-DAY(A1)+1 Enter this formula in A3 and copy down to A32: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Format A2:A32 as Date If A1 is empty the dates returned will be for January 1900. -- Biff Microsoft Excel MVP "Joe" wrote in message ... Is it possible to populate a column with the days of any given month by checking on a cell reference to a date? For example if A1 contained 2/28/2009 is there a way for the system to populate B1 through B28 with the dates of the month? Then if you changed A1 to 3/31/2009 have it populate B1 through B31 with the dates for March? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| # of days in that month for a selected date | Excel Discussion (Misc queries) | |||
| How to calculate a date: first day of the month after 60 days | Excel Discussion (Misc queries) | |||
| Date glitch? First 12 days of month format differently than the r | Excel Discussion (Misc queries) | |||
| Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
| If data in one column, take date, add 2 days, and turn the entire column a color... | Excel Discussion (Misc queries) | |||
Linear Mode
