@ > Home > Contents > A Shorter Next Month Formula Excel 4+, SpreadCE, Ooo, Lotus


A2: =DATE(YEAR(A1),MONTH(A1)+1,1) is a little clumsy for a formula increment of months' firsts. You need 3 functions for it.


A2: =A1+32-DAY(A1+31) needs only one an is also shorter. The only restriction is: Don't apply it on a 31st, in January not on 2 days before as well. The following month would be left out.

Of course you have Edit Fill Row Date Month, but this is not for formulas!

1st of quarter: =A1+93-DAY(A1+92)
10th of quarter: =A1+93-DAY(A1+83)
15th of 12-months duration: =A1+367-DAY(A1+352)

The second "hard number" subtracted from the first one results to the wished day. The first number is the maximum of days + 1, ensuring the formula's task.

Here you find each month span's hard number:

01 months: 032  02 months: 063  03 months: 093  04 months: 124  05 months: 154  06 months: 185
07 months: 216  08 months: 246  09 months: 277  10 months: 308  11 months: 338  12 months: 367