Month - first date and last date

Here's how to do it with a US format.
in B1 put
=DATEVALUE(A1&" 1, 2004")
and in B2 put
=EOMONTH(B1,0)
and format the cells to show as dates.
EOMONTH requires Excel's Analysis ToolPak add-in.

EDIT: I see Derk already beat me to this one. Also, I see I reversed B1 and B2 in my answer from the order you wanted.

First, if you don't already, in cell A1 enter an actual Excel date (e.g., 7/1/2004) and format it FORMAT > CELLS > CUSTOM > mmmm to display the date as the Month name in text.

Then in cell B1 enter:

=EOMONTH($A$1,0)

and in B2:

=EOMONTH($A$1,-1)+1

Then format B1 and B2 to get your UK date format.

Incidently, just in case you aren't sure how to get UK format in cells B1 and B2:

FORMAT > CELLS > CUSTOM > dd/mm/yyyy

should do it.

B1 =date(year(a1),month(a1),1)
B2 =date(year(a1),month(a1)+1,1)-1

Hth

Neale,
It only saves a couple of characters, but your B2 also could be:

=DATE(YEAR(A1),MONTH(A1)+1,0)

Similar but Different Query

Hi all!

Thanks for the post below, I have a similar sort of issue but I have month in B3 (March) then the year in C3 (2004), I would like D3 to use the information in these two cells to output the end of the month ie. 31 March 2004.

I tried =DATE(YEAR(B3),MONTH(B3)+1,0)
but I get #VALUE

Any ideas would be muchly appreciated.. . Many many thanks,

Philippe

Since B3 and C3 don't have Excel dates in them, you will need to use something like
=EOMONTH(DATEVALUE(B3&" 1, "&C3),0)
to get the end of the month.

#value

Thanks Derk, unfortunately it still gives me #VALUE error. I did an Error Check and it seems to go wrong when dealing with DATEVALUE(). Any ideas? Cheers,

Phil

Can you attach the workbook with the problem?

#value

I've been wondering as to whether it could not be related to the drop-down and the fact that it's not a number but the text. I've attached the workbook, as requested. Many many thanks!!!

Phil
Ps. I've tried the spreadsheet on my mac running Excel 2004 and, it works!! Still no luck on Win Excel 2002 though!?

Hi tekno

You simply need to adjust the row numbers in your formula to 4 instead of 3.

E.g.

=EOMONTH(DATEVALUE(B4&" 1, "&C4),0)

instead of

=EOMONTH(DATEVALUE(B3&" 1, "&C3),0)

regards,

m

Hair Pull!

Thanks M!

I had mistakenly uploaded an incorrect file, please find the new one. The formula works on my mac (Excel 2004) but not on my pc (Excel 2002) - using exactly the same file!

I have checked to make sure I have the correct Add-ins (Analysis ToolPak). Enabled and disabled them, restarted Excel, still no joy. Have I lost the plot?

Cheers,

Phil

Your workbook loads just fine on my PC - without any #VALUE errors.

I'm running Excel 2000 on Win 98 with Analysis Toolpak add-in active.

Suggest you activate ATP add-in prior to loading your workbook.

HTH.

m