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
1	2016-01-01	2016-02-05

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Arkusz1​

Worksheet Formulas Cell Formula B1 =IF(A1="","",DATE(YEAR(A1),MONTH(A1)+1,5)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[spycein]

Thank you so much Nard.. work like a charm

 

[nardagus]

Glad to help.

 

[vicedo]

Another option

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