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


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


    and in B2:


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

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


    There are three types of people in this world.
    Those who can count and those who can't.

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


  • #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,


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

    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.


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

    instead of

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



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



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



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!