Find Start & End of Month

[scottishmovies]

Hi Guys,

This has been driving me round the bend!

I want to put "Jan" (as in January) in a cell on Sheet1 and in cell A1. In cell A2 I want to dispay the start of the month and in cell A3 the end. So if I change A1 to "Feb" or any other month it will display the correct start and end dates.

I then want to use either the month or the dates to do a SUMIF statement. It a simple one like -

=IF(C5="","",SUMIF(April!R:R,"="&C5,April!G:G))

But I don't want April in the formula as I just want to update the cell in A1 to read in the data for a different month.
BTW, April is on another sheet along with a sheet for each month.

Hope that makes sense and someone can help me out?? This may not be the best way to do this, so if nyone has a suggestion to make it easier, PLEASE let me know!

All the best,
Pat

 

[Norie]

Pat

You can use this formula to get the first of the month.

=DATEVALUE("1 " & A1 & YEAR(TODAY))

and this formula for the last day.

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

For the ranges/sheet names in the SUMIF you can use INDIRECT.

=IF(C5="","",SUMIF(INDIRECT(A1 &"!R:R"),"="&C5,INDIRECT(A1 &"!G:G")))

 

[barry houdini]

Hello Pat, welcome to MrExcel

If you have the month as text in A1 use INDIRECT to get the relevant sheet, i.e.

=IF(C5="","",SUMIF(INDIRECT(A1&"!R:R"),C5,INDIRECT(A1&"!G:G")))

Make sure you use April not Apr if the sheet names use the full month name

 

[scottishmovies]

Hi Guys,

First of all - WOW! what a speedy response!
The INDIRECT statement works a tread - but the DATEVALUE one is throwing up a #NAME? error. Any ideas what I might be doing wrong?

 

[Norie]

My fault, it should be TODAY() not just TODAY.

=DATEVALUE("1 " & A1 & YEAR(TODAY()))

 

[barry houdini]

It should just work with

=DATEVALUE(1&A1)

The current year will be assumed

 

[scottishmovies]

Hi Norie,

That fixed the dates, they now display correctly, sort of
As it comes up with the date serial number, I assume I need to format it to display a date?
Also, the second date A3 is displaying the day/month BEFORE the date in A2.

 

[scottishmovies]

Just to clarify that last post.

Month in A1 = April
Date in A2 = 01/04/2013 (uk date!) Formula is =DATEVALUE(1&A1) although both versions above worked great!
Daate in A3 = 31/03/2013 Formula is =DATE(YEAR(A2), MONTH(A2),0)

 

[barry houdini]

I think Norie has a misplaced parenthesis, try this version

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

 

[Rick Rothstein]

It should just work with

=DATEVALUE(1&A1)

The current year will be assumed

Or even this...

=0+(1&A1)

And using a similar style construction, you can get the end of month date using this...

=(1&A1)+32-DAY((1&A1)+32)

Note 1: You will probably have to Cell Format each cell as a Date after entering them.

Note 2: Do not remove the parentheses... the formulas will not work without them.