# Find Start & End of Month

#### scottishmovies

##### Board Regular
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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")))

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

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?

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

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

It should just work with

=DATEVALUE(1&A1)

The current year will be assumed

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.

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)

I think Norie has a misplaced parenthesis, try this version

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

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.

Last edited:

Replies
2
Views
235
Replies
5
Views
335
Replies
8
Views
304
Replies
5
Views
235
Replies
18
Views
511

1,203,052
Messages
6,053,237
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back