3

I am working on projects that come in every 4 and a half month. I want to make a calender so I can see when the deadline is for each project.

I have a formula that adds up 4 months and 14 days. (4,5 months)

The formula works well but it works too well. I need a formula that adds up 4 months and 14 days so that each row starts with a date of either the first of a month or the 15th of a month.

=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...

  • 2
    What is the formula you used so far? – Dave Aug 27 '15 at 7:26
  • =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 Aug 27 '15 at 8:19
  • 1
    Nelis, 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 Aug 27 '15 at 8:44
1

enter image description here
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.

  • 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 Aug 27 '15 at 10:07
  • 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 Aug 27 '15 at 11:11
  • 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. i.imgur.com/YYEKtkw.png – Nelis Aug 27 '15 at 12:25
  • 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 Aug 27 '15 at 12:32
  • "online excel"? – Hannu Aug 27 '15 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.