correction formula given increase every 2 months

max_max

New Member
Joined
Jun 29, 2013
Messages
43
Hello everyone.
Is it possible in this forum for cross posting?
max_max
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

max_max

New Member
Joined
Jun 29, 2013
Messages
43
Hi to all.
I hope my English is understandable.
The formula in B6 increases the date of 2 months to every new year in B2
It is possible to increase not every January 1 but the day / month of the date in B2?
This post already posted here:
correction formula given increase every 2 months
The example is in post # 1
max_max
 

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi, max_max!

Place 2 or 3 handwritten examples of the result you want to get, so that we can help you better. Blessings!
 

max_max

New Member
Joined
Jun 29, 2013
Messages
43
Hello jonmpl.
I can not post an attachment, but there is another forum in post # 1.
An example is this:
in B6

=IF(B4>0,IF(ISERROR(INT((YEAR(B2)-YEAR(B4))/6)&" "&"anno/i -"&" "&MOD((YEAR(B2)-YEAR(B4)),6)*2&" "&"mese/i" ),"",INT((YEAR(B2)-YEAR(B4))/6)&" "&"anno/i -"&" "&MOD((YEAR(B2)-YEAR(B4)),6)*2&" "&"mese/i" ),"")


If B4 is not empty
Every new year in B2
The formula in B6 increases by 2 months

example:
B2= 01/01/2000
B4= 01/05/2000

B2= 01/01/2001
B4= 01/05/2000
B6= 0 anno/i - 2 mese/i

B2= 01/01/2002
B4= 01/05/2000
B6= 0 anno/i - 4 mese/i

B2= 01/01/2006
B4= 01/05/2000
B6= 1 anno/i - 0 mese/i

The change is
The formula must not start any new year's but the month of the date in B4.
I hope I have explained.
max_max
 

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi, again!

Is this? (in B6) :
=IF(B4,IFERROR(INT(DATEDIF(B4,B2,"y")/6)&" anno/i - "&2*MOD(DATEDIF(B4,B2,"y"),6)&" mese/i",""),"")

Try and comment! Blessings!
 

max_max

New Member
Joined
Jun 29, 2013
Messages
43
HI johnmpl,
Your formula I think is right.
It is possible to modify your formula: that when in B6 the formula becomes:
5 anno/i - 0 mese/
Even adding more years to B2 your formula stops?
I hope I've been understandable.
Anyway thanks for your formula.
max_max
 

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
HI johnmpl,
Your formula I think is right.
It is possible to modify your formula: that when in B6 the formula becomes:
5 anno/i - 0 mese/
Even adding more years to B2 your formula stops?
I hope I've been understandable.
Anyway thanks for your formula.
max_max
Try:
=IF(B4,IFERROR(MIN(INT(DATEDIF(B4,B2,"y")/6),5)&" anno/i - "&2*MOD(MIN(DATEDIF(B4,B2,"y"),30),6)&" mese/i",""),"")

Blessings!
 

max_max

New Member
Joined
Jun 29, 2013
Messages
43
HI johnmpl,
New formula is o.k. :)
Thank you so much.
Your formula in italian is:
=SE(B4;SE.ERRORE(MIN(INT(DATA.DIFF(B4;B2;"y")/6);5)&" anno/i - "&2*RESTO(MIN(DATA.DIFF(B4;B2;"y");30);6)&" mese/i";"");"")
max_max
 

max_max

New Member
Joined
Jun 29, 2013
Messages
43
HI johnmpl.
Your new formula:

=IF(B4,IFERROR(MIN(INT(DATEDIF(B4,B2,"y")/6),5)&" anno/i - "&2*MOD(MIN(DATEDIF(B4,B2,"y"),30),6)&" mese/i",""),"")

work in excel 2003?
max_max

 

Watch MrExcel Video

Forum statistics

Threads
1,102,660
Messages
5,488,161
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top