Rounding Dates up to the 15th or the last day of the month

**karenbr** · 10-11-2010, 12:54 PM

I'm trying to identify a list of effective dates to the respective pay periods by using a rounding formula so for example, an effective date of 8/2/10 would be in the 8/15/10 pay period and 8/16/10 would be in the 8/31/10 pay period. Any help would be awesome!

**DonkeyOte** · 10-11-2010, 01:11 PM

What happens if the 15th or last day of month is a non-working day ?

**karenbr** · 10-11-2010, 01:20 PM

Originally Posted by DonkeyOte

What happens if the 15th or last day of month is a non-working day ?

It doesn't matter if it's a working or non-working day. I'm looking for the pay period, not the actual pay date.

**DonkeyOte** · 10-11-2010, 01:25 PM

Numerous approaches in truth

Please Login or Register  to view this content.

if you prefer to avoid EOMONTH

Please Login or Register  to view this content.

**karenbr** · 10-11-2010, 01:41 PM

Thanks! I just stumbled through a similar solution. I found a formula to calculate for first day of the month and added 14 to get the 15th, and then found another formula that calculated the last day of the month, but yours looks much simpler!

=IF(DAY(A1)<16,(DATE(YEAR(A1),MONTH(A1),1))+14,(DATE(YEAR(A1),MONTH(A1)+1,0)))

**EPeterson** · 12-12-2011, 04:39 PM

How does this formula account for leap year?

I have a contract that should start on 2/10/12 and should be effective until the 31st day in January 2013 but the formula gives me 1/29/13. I am using this to calculate other contracts as well so it needs to work for other months (ie: a contract starting 3/4/12 should calculate an expiration date of 2/28/13).

I have tried both of these formulas:
=EDATE(IF(DAY(Q15)<1,Q15+31-DAY(Q15),EOMONTH(Q15,0)),11)

=EDATE(IF(DAY(Q16)<1,(DATE(YEAR(Q16),MONTH(Q16),1))+31,(DATE(YEAR(Q16),MONTH(Q16)+1,0))),11)

Thanks.

**daddylonglegs** · 12-12-2011, 04:52 PM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.