=DATEVALUE

[JR]

In A1 I have (formatted in text with a list of all months) the month of
January is spelled out for this example.

In A2 I have the following formula: =DATEVALUE(A$1&" 1")

And then I have: =A2+1, A3+1, etc all the way down to the 31st of the month.

Ultimately the rest of the sheet will be used with vlookups to bring in data.

The help I need is when we are going into short months like February. How
can I get the last three days of the month to be blank if it does not match
the month selected in the list (A1)?

Thanks


January


Monday, January 02, 2006
Tuesday, January 03, 2006
Wednesday, January 04, 2006
Thursday, January 05, 2006
Friday, January 06, 2006
Saturday, January 07, 2006
Sunday, January 08, 2006
Monday, January 09, 2006
Tuesday, January 10, 2006
Wednesday, January 11, 2006
Thursday, January 12, 2006
Friday, January 13, 2006
Saturday, January 14, 2006
Sunday, January 15, 2006
Monday, January 16, 2006
Tuesday, January 17, 2006
Wednesday, January 18, 2006
Thursday, January 19, 2006
Friday, January 20, 2006
Saturday, January 21, 2006
Sunday, January 22, 2006
Monday, January 23, 2006
Tuesday, January 24, 2006
Wednesday, January 25, 2006
Thursday, January 26, 2006
Friday, January 27, 2006
Saturday, January 28, 2006
Sunday, January 29, 2006
Monday, January 30, 2006
Tuesday, January 31, 2006
Cummulative/Actual

 

[bpeltzer]

You can embed the datevalue function inside an IF:
=if(month(datevalue(A$1&" 29"))=month(A$2),datevalue(A$1&" 29"),"")
BTW, you could save yourself some typing by using =datevalue(A$1 & row()-1)
and autofill.
--Bruce

 

[Bob Phillips]

In A3

=IF(ISERROR(MONTH(A2+1)),"",IF(MONTH(A2+1)<>MONTH(A2),"",A2+1))


and copy doiwn

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[JR]

Thanks for the help, so how do I use the solutions you provided?

I noticed the formula has 29. Is this formula good only for February? How
do I use it for the rest of the year?

Also, how do I use the one that saves me typing? Do I use it with the first
solution you provided, or separately?

Thanks

 

[bpeltzer]

You could use the 29, and similar functions for 30 and 31, for ANY month.
Just replace the functions you've got now for the 29th, 30th and 31st.
As for saving the typing, it can be even easier:
In A2: =datevalue(a$1 & 1).
In A3: =a2+1
Autofill A3 down through A29 (the 28th of the month).
In A30: =IF(MONTH(A27+3)=MONTH(A$2),A27+3,"")
Autofill A30 down through A32.
--Bruce

 

[JR]

perfect

In A3

=IF(ISERROR(MONTH(A2+1)),"",IF(MONTH(A2+1)<>MONTH(A2),"",A2+1))


and copy doiwn

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)