# Month - first date and last date

• Hi all,

This should be simple, but can't seem to find a suitable formula.

If I put the name of a month in cell A1 for instance (June)

I want to have B1 be 01/06/2004
and B2 be 30/06/2004

(UK date format obviously)

If I change A1 to be July, I want July start / end dates in there....

what formula should I use?

any offers gratefully recieved...

thanks.....Rod

• 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

There are three types of people in this world.
Those who can count and those who can&#039;t.

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

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

• Thanks guys - been out of office all week, but I've come back and you've done it again - many thanks...Rod

• 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

• #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!?

## Files

• Hi tekno

E.g.

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

=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

## Files

• 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.