Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
anybody got any ideas how i can display a lit of dates in cells a2:a32
that are the dates of the month in cell a1? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
the dates of the month in cell a1?
How is the month entered in the cell? As the month name as a TEXT entry: January or Jan As a true Excel date: 1/1/2008 As the number of the month: 1 ??? -- Biff Microsoft Excel MVP "Esra" wrote in message ... anybody got any ideas how i can display a lit of dates in cells a2:a32 that are the dates of the month in cell a1? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
The month in cell a1 says January, or Febuary, or March,,,,,,
On Mon, 31 Mar 2008 23:22:27 -0400, "T. Valko" wrote: the dates of the month in cell a1? How is the month entered in the cell? As the month name as a TEXT entry: January or Jan As a true Excel date: 1/1/2008 As the number of the month: 1 ??? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
And what do you want as the date result? The first of the month? the end of
the month? The current year? or some other year? Regards, Fred. "Esra" wrote in message ... The month in cell a1 says January, or Febuary, or March,,,,,, On Mon, 31 Mar 2008 23:22:27 -0400, "T. Valko" wrote: the dates of the month in cell a1? How is the month entered in the cell? As the month name as a TEXT entry: January or Jan As a true Excel date: 1/1/2008 As the number of the month: 1 ??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
In A2: =TEXT(DATE(YEAR(TODAY()),MONTH($A$1&1),ROW(A1)),"m/d/yyyy")
"Esra" wrote: anybody got any ideas how i can display a lit of dates in cells a2:a32 that are the dates of the month in cell a1? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
Try this:
A1 = month name as a TEXT entry: February Enter this formula in A2: =IF(A1="",TODAY()-DAY(NOW())+1,--("1-"&A1)) Format as DATE Enter this formula in A3 and copy down to A32: =IF(MONTH(A$2+ROWS(A$3:A3))=MONTH(A$2),A$2+ROWS(A$ 3:A3),"") Format as DATE These formulas will return the dates for the month entered in A1 of the *current year*. -- Biff Microsoft Excel MVP "Esra" wrote in message ... The month in cell a1 says January, or Febuary, or March,,,,,, On Mon, 31 Mar 2008 23:22:27 -0400, "T. Valko" wrote: the dates of the month in cell a1? How is the month entered in the cell? As the month name as a TEXT entry: January or Jan As a true Excel date: 1/1/2008 As the number of the month: 1 ??? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
I never thought that such as simple question could turn out to be so
hard. It must be how i am describing it. In cell A1 I want to enter a month, i guess i will have to also put a year, not sure how i would ebnter that but am open to suggestions. Then in the subsequent 31 cells below a1, i wish the dates to be put. I would like format for date to be Tuesday 1st April. I dont need the year, but realise I will have to have that there to tell what day of the week it is. eg: April Tuesday 1st April Wednesday 2nd April Thiursday 3rd April ,,, ,,, ,,, ,,, Thuirsday, 30 April I realise only 30 days in April and not 31, but can manually delete unrequired dates Does that make sense now? Sorry about confusion. Esra TIA On Mon, 31 Mar 2008 21:16:01 -0700, Teethless mama wrote: In A2: =TEXT(DATE(YEAR(TODAY()),MONTH($A$1&1),ROW(A1)),"m/d/yyyy") "Esra" wrote: anybody got any ideas how i can display a lit of dates in cells a2:a32 that are the dates of the month in cell a1? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
PERFECT, exactly what I wanted. Thank you so much.
Esra On Tue, 1 Apr 2008 00:45:07 -0400, "T. Valko" wrote: Try this: A1 = month name as a TEXT entry: February Enter this formula in A2: =IF(A1="",TODAY()-DAY(NOW())+1,--("1-"&A1)) Format as DATE Enter this formula in A3 and copy down to A32: =IF(MONTH(A$2+ROWS(A$3:A3))=MONTH(A$2),A$2+ROWS(A $3:A3),"") Format as DATE These formulas will return the dates for the month entered in A1 of the *current year*. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Esra" wrote in message ... PERFECT, exactly what I wanted. Thank you so much. Esra On Tue, 1 Apr 2008 00:45:07 -0400, "T. Valko" wrote: Try this: A1 = month name as a TEXT entry: February Enter this formula in A2: =IF(A1="",TODAY()-DAY(NOW())+1,--("1-"&A1)) Format as DATE Enter this formula in A3 and copy down to A32: =IF(MONTH(A$2+ROWS(A$3:A3))=MONTH(A$2),A$2+ROWS( A$3:A3),"") Format as DATE These formulas will return the dates for the month entered in A1 of the *current year*. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
Hi,
Just for the hell of it: in cell A2: =DATEVALUE("1-"&IF(A1="",MONTH(NOW()),A1)) in cell A3: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Drag A3 down to A32. Cheers, Ivan. On Apr 1, 3:57*pm, Esra wrote: I never thought that such as simple question could turn out to be so hard. *It must be how i am describing it. In cell A1 I want to enter a month, i guess i will have to also put a year, not sure how i would ebnter that but am open to suggestions. Then in the subsequent 31 cells below a1, i wish the dates to be put. I would like format for date to be Tuesday 1st April. *I dont need the year, but realise I will have to have that there to tell what day of the week it is. eg: April Tuesday 1st April Wednesday 2nd April Thiursday 3rd April ,,, ,,, ,,, ,,, Thuirsday, 30 April I realise only 30 days in April and not 31, but can manually delete unrequired dates Does that make sense now? Sorry about confusion. Esra TIA On Mon, 31 Mar 2008 21:16:01 -0700, Teethless mama wrote: In A2: =TEXT(DATE(YEAR(TODAY()),MONTH($A$1&1),ROW(A1)),"m/d/yyyy") "Esra" wrote: anybody got any ideas how i can display a lit of dates in cells a2:a32 that are the dates of the month in cell a1?- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding date of the month
Hi Again,
Actually even shorter would be: in Cell A2: =--("1-"&IF(A1="",MONTH(NOW()),A1)) That double minus sign's a nifty trick Biff! You learn something new each day! Cheers, and thanks again Biff. Ivan. On Apr 1, 6:33*pm, Ivyleaf wrote: Hi, Just for the hell of it: in cell A2: =DATEVALUE("1-"&IF(A1="",MONTH(NOW()),A1)) in cell A3: =IF(DAY(IF(A2="",0,A2)+1)DAY(A$2),A2+1,"") Drag A3 down to A32. Cheers, Ivan. On Apr 1, 3:57*pm, Esra wrote: I never thought that such as simple question could turn out to be so hard. *It must be how i am describing it. In cell A1 I want to enter a month, i guess i will have to also put a year, not sure how i would ebnter that but am open to suggestions. Then in the subsequent 31 cells below a1, i wish the dates to be put. I would like format for date to be Tuesday 1st April. *I dont need the year, but realise I will have to have that there to tell what day of the week it is. eg: April Tuesday 1st April Wednesday 2nd April Thiursday 3rd April ,,, ,,, ,,, ,,, Thuirsday, 30 April I realise only 30 days in April and not 31, but can manually delete unrequired dates Does that make sense now? Sorry about confusion. Esra TIA On Mon, 31 Mar 2008 21:16:01 -0700, Teethless mama wrote: In A2: =TEXT(DATE(YEAR(TODAY()),MONTH($A$1&1),ROW(A1)),"m/d/yyyy") "Esra" wrote: anybody got any ideas how i can display a lit of dates in cells a2:a32 that are the dates of the month in cell a1?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help finding the date of the last Saturday of a given month | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
Finding the date on the 'nth' Monday in this Month in this Year | Excel Worksheet Functions | |||
Finding min and max date of a given month | Excel Worksheet Functions |