+ Reply to Thread
Results 1 to 8 of 8

correction formula given increase every 2 months

  1. #1
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    correction formula given increase every 2 months

    Hi to all.
    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?
    I attach example.
    max_max
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: correction formula given increase every 2 months

    In B4 copied down every two rows

    =DATE(YEAR($B$2),MONTH($B$2)+ROWS($B$2:$B4), DAY($B$2))

    Note: If the month in question has less days than the month in B2, the result will end up in the next month. For example, 7/31 to February will be 3/3/2018 as there are only 28 days in Feb.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: correction formula given increase every 2 months

    Hi chemistB,
    That's no good.
    You can not add rows or edit cells in the post example.
    You can only modify the formula in cell B6 in other cells there are other formulas.
    max_max

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: correction formula given increase every 2 months

    So you want the date in B6 to have the same "Day" as in B2?

  5. #5
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: correction formula given increase every 2 months

    Yes
    The formula in B6 must increase by 2 months the same day / month that is in B2

  6. #6
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: correction formula given increase every 2 months

    Hi,
    Has anyone understood how to solve the formula?
    max_max

  7. #7
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: correction formula given increase every 2 months

    Hello
    I modified the formula:

    =SE(MESE(B2)=MESE(B4);SE(B4>0;SE(VAL.ERRORE(INT((ANNO(B2)-ANNO(B4))/6)&" "&"anno/i -"&" "&RESTO((ANNO(B2)-ANNO(B4));6)*2&" "&"mese/i");"";INT((ANNO(B2)-ANNO(B4))/6)&" "&"anno/i -"&" "&RESTO((ANNO(B2)-ANNO(B4));6)*2&" "&"mese/i");""))

    =IF(MONTH(B2)=MONTH(B4),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"),""))


    Works in part, ie the years / months are displayed only when the months of dates in B4 / B6 are the same.
    They should always look like the previous formula:

    =SE(B4>0;SE(VAL.ERRORE(INT((ANNO(B2)-ANNO(B4))/6)&" "&"anno/i -"&" "&RESTO((ANNO(B2)-ANNO(B4));6)*2&" "&"mese/i" );"";INT((ANNO(B2)-ANNO(B4))/6)&" "&"anno/i -"&" "&RESTO((ANNO(B2)-ANNO(B4));6)*2&" "&"mese/i" );"")

    =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" ),"")

    an help?
    I attach example.
    The new formula in C6
    max_max
    Attached Files Attached Files
    Last edited by max_max; 08-01-2017 at 02:04 PM.

  8. #8
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: correction formula given increase every 2 months

    Hello
    Maybe I did not explain it?
    max_max

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-22-2014, 02:15 AM
  2. Replies: 8
    Last Post: 02-20-2014, 05:46 PM
  3. Replies: 0
    Last Post: 04-13-2012, 03:10 AM
  4. Percentage Increase for 36 months of prices
    By tylwddteg in forum Excel General
    Replies: 2
    Last Post: 12-07-2011, 05:41 PM
  5. Increase months by number from next column
    By harishnehru in forum Excel General
    Replies: 5
    Last Post: 05-24-2006, 07:19 AM
  6. [SOLVED] Only with a formula, how do I increase the months?
    By Greg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2006, 11:15 AM
  7. [SOLVED] how do I forecast a 15% increase over 7 months?
    By vrosen in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1