0

Cell A1 shows the Start Date, e.g. 17/09/15

Cell B1 will need to reflect the End Date.End Date need to be based on 3 months of Start Date.

I can't just use 90 days as standard days to add to the Start Date as different months have different no of days. Sept has 30 days, Oct 31 days and Nov 30 days. Hence total number of days will be 91 days.

Is there any way excel can auto calculate the number of days in that specific month and add 3 months of days to Start Date?

2

Split the date up into year/month/day and add 3 months:

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

assuming A1 holds your start date.

  • Thanks user1016274. Just a question why the day does not varies? E.g. 01/12/15. 3 Months [31 days (Dec) + 31 days (Jan) + 29 days (Feb) = 91 days (Total)], will end on 29/02/16. Based on your formula, the date i obtained is 01/03/16. Anyway the day can varies due to different total number of days per month? Thanks – Brandon Sep 29 '15 at 16:42
  • No, if you only add whole months then the day will not change: 14th of May -> 14th of August. It will change if you add 90 days but you wanted to avoid that. The year will always adjust to the new date. – user1016274 Sep 29 '15 at 16:42
  • Dec 1st + 31 days is Dec 32nd==Jan 1st, you're one day off. If you count whole months the day will never change regardless how long the months are. – user1016274 Sep 29 '15 at 16:47
  • in our work flow, we need to take the day into account. Thanks for your kind advise. – Brandon Sep 29 '15 at 17:21
  • If you need exactly ONE day more (one month plus one day) to account for the current day, then change the formula to =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)+1). Even then, the end day will always be the next day after start_day. – user1016274 Sep 29 '15 at 17:29
1

Note: This is just to show how to get the number of days.

Here's how i set it up, so feel free to change it once you understand how it works. I set it up by putting the number of days from C1-C12 with D2 being a leap year (feel free to change the code to reflect numbers instead of cells)

A1 = start date A2 = MONTH(A1) A2 = MONTH(A1)+1 A3 = MONTH(A1)+2

B1 = =A1+B5

B2 = =IF(A2=1,C1,IF(A2=2,IF((MOD(YEAR(A1),4))=0,D2,C2),IF(A2=3,C3,IF(A2=4,C4,IF(A2=5,C5,IF(A2=6,C6,IF(A2=7,C7,IF(A2=8,C8,IF(A2=9,C9,IF(A2=10,C10,IF(A2=11,C11,IF(A2=12,C12,0))))))))))))

B3 = =IF(A3=1,C1,IF(A3=2,IF((MOD(YEAR(A1),4))=0,D2,C2),IF(A3=3,C3,IF(A3=4,C4,IF(A3=5,C5,IF(A3=6,C6,IF(A3=7,C7,IF(A3=8,C8,IF(A3=9,C9,IF(A3=10,C10,IF(A3=11,C11,IF(A3=12,C12,0))))))))))))

B4 = =IF(A4=1,C1,IF(A4=2,IF((MOD(YEAR(A1),4))=0,D2,C2),IF(A4=3,C3,IF(A4=4,C4,IF(A4=5,C5,IF(A4=6,C6,IF(A4=7,C7,IF(A4=8,C8,IF(A4=9,C9,IF(A4=10,C10,IF(A4=11,C11,IF(A4=12,C12,0))))))))))))

B5 = =SUM(B2,B3,B4)

This is just the long way around, but my point of doing this is for B5, this will give you the number of days between each month, but really the day of the month is always going to be the same. I threw B1 in there just to show that as well, but you need to set all cells to be general or a number, and A1 and B1 are dates for the cell type.

A2-4 are the 3 months, B2-4 are the number of days in those months, and B5 is the total number of days. This includes a leap year, but if you just want a straight 3 months after, use the answer user1016274 gave you.

Have fun! :)

  • lol...thanks for your kind arise. Will need some time to digest. Will try it out shortly. Thanks. – Brandon Sep 29 '15 at 17:23
  • I can break it down a bit if you need me to, but basically it's just a bunch of if functions nested within each other to give you the number of days. That is the worst of it, since you have to ask what month it is. There may be an easier way, but that's what i can give you right away. – dakre18 Sep 29 '15 at 17:38
1

@Brandon, I would like to suggest you 3 different Formula, considering your requirements. Working properly. Check the Screen below.

enter image description here

Formula 1: =EDATE(A261,3)

Formula 2: =DATE(YEAR(A261),MONTH(A261)+3,DAY(A261))

Formula 3: =IF(OR(MONTH(A261)={1,12}),(A261+90),IF(OR(MONTH(A261)={4,9}),(A261+91),IF(OR(MONTH(A261)={3,5,6,7,8,10,11}),(A261+92),(A261+89))))

NB: Change the data range as you need, don't forget to reply. For the Formula 3 as per the Month I've calculated total days for 3 Months.

Like if Month is 1 ie January then Excel add 90 days (31 for Jan + 28 for Feb + 31 for Mar) and so for others.

Hope this help you.

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.