Formula to generate last day of month

**rizmomin** · 02-20-2012, 03:15 PM

Hi :

I need to know if excel have a formula to generate date for middle of the month and last day of the month.

Example:

1/15/2012
1/31/2012
2/15/2012
2/29/2012
etc...

Thanks

RM

**royUK** · 02-20-2012, 03:19 PM

Assuming the date is in D1 this formula will return last day of the month

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

Not sure about the middle

**DanoT** · 02-20-2012, 03:28 PM

Assuming cell D1 = 2/1/12, the following formula should return whichever day you specify (in this case, I have assumed the 15th):

=DATE(YEAR(D1),MONTH(D1), 15)

**royUK** · 02-20-2012, 03:28 PM

This might work for middle of the month

=DATE(YEAR(D1),MONTH(D1),DAY(DATE(YEAR(D1),MONTH(D1),0))/2)

As you are using Excel 2010

last day of month:

=EOMONTH(D1,0)

middle day of month

=DATE(YEAR(D1),MONTH(D1),DAY(EOMONTH(D1,0))/2)

For the above in earlier versions you would need the Analysis ToolPak installed

**daddylonglegs** · 02-20-2012, 03:43 PM

If you want a series of dates like that then put the first date in A1 and then this formula in B1 copied as far as you need

=IF(DAY(A1)=15,17-DAY(A1+17),15)+A1

**rizmomin** · 02-20-2012, 03:55 PM

Hi ALL;

This will surely work for my project.
I still am looking for help on my tread "Working Week Split"

i would appreciate if someone can have a look and help me with this.

This is in part of coming up a solution for calcualting overtime if paid semi-monthly.

Please let me know if you have any questions.

Thank you all for helping ...

RM

**fzz** · 02-20-2012, 04:02 PM

If the middle of the month would always be the 15th, which seems to be implied from your example where it's the middle of 31-day January and 29-day February, then given a date in cell X99, the 15th day of that date's month is given by

=X99-DAY(X99)+15

Last day of the month for the date in X99 could also be given by

=X99-DAY(X99)+32-DAY(X99-DAY(X99)+32)

That said, since the OP indicates using Excel 2010, just use EOMONTH,

=EOMONTH(X99,0)

**rizmomin** · 02-20-2012, 04:25 PM

Hi ALL:

Please refer tto the attached sheet to see what i am trying to accomplish.

I need to get a date range START END as shown in Column N and O.
I also need to create a calender like shown in left side..
Thanks and please let me know if u have questions

RM