Excel formula for 5th day of next month

[spycein]

Hi Everyone,

I am looking for a formula which would return the value as 5th day of next month based on any random date of current month (from 1 to 31 ).

For Example, My Cell "A1"contains 15-May-2016, i need a formula in "B1" which will return the value as 05-June-2016.

Similarly, if i change the value of "A1" as 28-May-16 then also Cell "B1" will return the value as 05-June-2016.
if i change the value of "A1" as 20-Aug-16 then also Cell "B1" will return the value as 05-Sep-2016.

Thanks

 

[nardagus]

Hello,

Something like this?

Excel 2013

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2016-01-01[/TD]
[TD="align: right"]2016-02-05[/TD]

</tbody>

Arkusz1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IF(A1="","",DATE(YEAR(A1),MONTH(A1)+1,5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[spycein]

Thank you so much Nard.. work like a charm

 

[nardagus]

Glad to help.

 

[vicedo]

Another option

=IF(A1="","",EOMONTH(A1,0)+5)