Excel date month issue

The formula is:
=IF(DAY(A2)<10,EOMONTH(A2,3)+15,EOMONTH(A2,4)+1):

if the original date is at the end of the month DAY(A2)<10, then it's shifted by 4 month (3 months plus end of the month) +15 is the 15th of the desired month.
if the original date is middle-month, then +4.5 months means 1st of 5th month (end of 4th month + 1 days).

The formula also works across years.

Máté Juhász

Posted 2015-08-27T07:24:58.223

Reputation: 16 807

Thanks for your reply, but excel does not accept this formula. Also, I am confused by the DAY<10 argument. Am I also required to fill in the dates manually in column A? The projects are given to us by external companies. There are multiple people working on multiple projects. Each project has 4,5 month to complete, because then, the next project will be supplied. We cannot accept these projects any time sooner within this 4,5 month cycle, as the projects are delivered to us on every 1st and 15th of the month. It would be nice if I could automatically make an overview for me and my coworkers. – Nelis – 2015-08-27T10:07:01.537

What do you mean by Excel doesn't accept this formula? What error do you receive? Of course you can refer to any cells, not necessarily to a cell in column A. Without seeing more from your data I can't help you more. – Máté Juhász – 2015-08-27T11:11:06.937

The general error "There is a problem with this formula". It is in Dutch, sorry I don't know how it is translated from English but it is the general error message. I translated the formula to Dutch but this doesn't change anything.

http://i.imgur.com/YYEKtkw.png

– Nelis – 2015-08-27T12:25:39.610

There is at least one error in your formula, missing , at +15LAATSTE. If you don't have English version installed on your pc you can still go for on-line Excel where you can change language to English and insert my formula easily instead of manually translating it. – Máté Juhász – 2015-08-27T12:32:10.837

"online excel"? – Hannu – 2015-08-27T17:45:42.453

@hannu: Yes, upload the file to OneDrive and open it. – Máté Juhász – 2015-08-27T19:03:45.790

Well, that is only an option of you have opted in on Microsoft - I for one have not, I'm all Linux and will probably remain so for forseeable future. For what it is worth: The translation of function names in Excel is a total abomination and should be condemned forcefully. – Hannu – 2015-08-28T07:49:38.737

@hannu: this question is asking help for Excel, if you opted for Linux, than it's probably not interesting for you. – Máté Juhász – 2015-08-28T07:52:20.830

=) LibreOffice might not be 100% equal to Excel, especially w.r.t. VBA - but the compatibility is strikingly high, and very very close to 100% when it comes to any sheet-functions. – Hannu – 2015-08-28T07:55:24.687

2What is the formula you used so far? – Dave – 2015-08-27T07:26:51.853

=DATE(YEAR(A1)+0,MONTH(A1)+4,DAY(A1)+15)

(I tried to make an extra row from here with a -IF DAY value greater/smaller as 15 but I can't seem to get it working at all. My excel expertise is limited, I'm afraid...) – Nelis – 2015-08-27T08:19:19.577

1Nelis, this is a Q&A site, not a forum. This means, it's totally cool (and encouraged) to edit your original post! :) You hopefully can see the edit link in the post itself. This is a good first question, +1 – Dave – 2015-08-27T08:44:19.407

Excel date month issue

Answers