CODEWITHSUNDEEP
excelopenoffice.orglibreoffice
Jake Petroules
Jake Petroules

Reputation: 24140

How can I calculate the next coming X day of the month in LibreOffice / Excel?

I have an expenses spreadsheet which I'd like to show the next due date for a bill due on the nth day of each month.

For example, assuming said bill is due on the 15th of each month:

=FOO(2014-02-14, 15) => 2014-02-15
=FOO(2014-02-15, 15) => 2014-02-15
=FOO(2014-02-16, 15) => 2014-03-15

I know of EOMONTH and EDATE but I'm not sure how to build a formula that does exactly what I need.

Upvotes: 2

Views: 1183

Answers (3)

barry houdini
barry houdini

Reputation: 46331

For any date in A1 this formula in Excel gives you the next 15th of the month

=EOMONTH(A1-15,0)+15

....so in your example if A1 is 14th Feb 2014 that will give you 15th Feb 2014, and the same with 15th Feb 2014, but when A1 is 16th Feb 2014 that formula will give you 15th March 2014

Upvotes: 3

Tom
Tom

Reputation: 218

enter image description here

The formula in B1 is =DATE(YEAR($A1),IF(DAY($A1)<=15,MONTH($A1),MONTH($A1)+1),15). If you need to base your due dates off today's date, just replace $A1 with TODAY(). Hope this helps.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If A1 contains a Date and B1 contains a number, like 20, then in another cell enter:

=DATE(YEAR(A1),MONTH(A1),B1)

and if you want to force this into the future, use:

=IF(DATE(YEAR(A1),MONTH(A1),B1)>TODAY(),DATE(YEAR(A1),MONTH(A1),B1),DATE(YEAR(A1),MONTH(A1)+1,B1))

Upvotes: 2

Related Questions