|
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 02:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com