How to round dates up or down?

Posted by Brad on July 26, 2001 6:17 AM

I need to round all dates that are between the 2nd and 15th down to the 1st and all dates greater than the 15th to the 1st of the next month.
i.e. 1/3/01 -> 1/1/01
1/14/01 -> 1/1/01
1/16/01 -> 2/1/01

Any suggestions? I could use either a formula or macro.

Brad

Posted by Mark W. on July 26, 2001 6:27 AM

=((DAY(A1)>15)+MONTH(A1)&"/1")+0

Posted by Aladin Akyurek on July 26, 2001 6:30 AM

Brad,

One way of doing it would be:

=IF(AND(DAY(A1)>=1, DAY(A1)<15), DATE(YEAR(A1), MONTH(A1),1), DATE(YEAR(A1), MONTH(A1)+1,1))

where A1 contains the date to round.

Aladin

Posted by Ian on July 26, 2001 6:53 AM

What about Feb and do we assume 31 days 16 needs to be rounded up?

Posted by Ian on July 26, 2001 6:55 AM

Not the original question, just interested.

Posted by Aladin Akyurek on July 26, 2001 7:03 AM

Re: Not the original question, just interested.

Hi Ian,

I tried out the formula on Feb-dates. It will round up both 2/28 and 2/29 to 3/1. I reckon such an assumption is unneeded here. Below 15 the first of the same month, above 15 the first of the next month. That is what Brad appears to be asking.

Aladin

Posted by Brad on July 26, 2001 7:05 AM

Yes, in Feb if>15 then go March 1....looks good

Posted by Aladin Akyurek on July 26, 2001 7:12 AM

Amazing...

that it comes out right on 16/12/01!

Posted by Mark W. on July 26, 2001 7:25 AM

Minor repair to fix year roll over...if interested

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>15),1) =((DAY(A1)>15)+MONTH(A1)&"/1")+0 : I need to round all dates that are between the 2nd and 15th down to the 1st and all dates greater than the 15th to the 1st of the next month.

Posted by Aladin Akyurek on July 26, 2001 7:53 AM

Nice & short..

Brad-- If still interested in the longer formula [ :-) ], make the part DAY(A1)<=15.

Aladin