Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all,
Vanilla are planning an update to the site on April 24th (next Wednesday). It is a major PHP8 update which is expected to boost performance across the site. The site will be down from 7pm and it is expected to take about an hour to complete. We appreciate your patience during the update.
Thanks all.

Excel formula

  • 11-12-2020 11:48am
    #1
    zetor 4911
    Registered Users Posts: 421 ✭✭


    Hi,
    Maybe someone could help me with an excel formula. If I enter say March in cell A1 I want to write a formula in another cell (A2) that will add 1 month to cell A1 so April should be in cell A2 anyone have any solution? Thanks in advance


Comments

  • #2
    kevincaomhin
    Registered Users Posts: 72 ✭✭kevincaomhin


    zetor 4911 wrote: »
    Hi,
    Maybe someone could help me with an excel formula. If I enter say March in cell A1 I want to write a formula in another cell (A2) that will add 1 month to cell A1 so April should be in cell A2 anyone have any solution? Thanks in advance
    The following could work for limited number of rows;

    If you enter a date in cell A1 - 11/12/2020
    Change format of cell to custom "mmmm"
    it should show December
    Go to cell A2 and enter formula =A1 +15
    That should show January
    etc etc.


  • #3
    phonypony
    Registered Users Posts: 1,306 ✭✭✭phonypony


    I would use edate- Use the cell formatting as Kevin says, then in A2: =edate(A1, 1)


  • #4
    [Deleted User]
    Posts: 0 ✭ [Deleted User]


    zetor 4911 wrote: »
    Hi,
    Maybe someone could help me with an excel formula. If I enter say March in cell A1 I want to write a formula in another cell (A2) that will add 1 month to cell A1 so April should be in cell A2 anyone have any solution? Thanks in advance

    In A1 type March, in B1 type April. then highlight both cells and drag the fill button at the bottom right hand corner of the selected cell to the right and it will automatically fill in the months.


  • #5
    Jimbob1977
    Registered Users Posts: 2,239 ✭✭✭Jimbob1977


    Automatically (no need for formulae):

    1. Write "Mar" or "Mar-2020" in Cell A1

    2. Drag Cell A1's bottom-right hand corner by the cursor


    It will populate the coming months indefinitely.

    It can be dragged left-to right or up-to-down

    To your heart's content.


  • #6
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,802 Mod ✭✭✭✭Seth Brundle


    If the word "March" is in cell A1 then use the following formula... 
    =TEXT(EDATE(DATEVALUE("01-" & A1 & "-2020"),1),"mmmm")

    DATEVALUE("01-" & A1 & "-2020") allows me to convert the word to an actual date.
    EDATE allows me to add one month to our date
    TEXT allows me to format our new date in mmmm (full month name) format


  • Advertisement
  • #7
    zetor 4911
    Registered Users Posts: 421 ✭✭zetor 4911


    kevincaomhin wrote: »
    The following could work for limited number of rows;

    If you enter a date in cell A1 - 11/12/2020
    Change format of cell to custom "mmmm"
    it should show December
    Go to cell A2 and enter formula =A1 +15
    That should show January
    etc etc.

    Thanks for that. I prefer to not enter the date in cell A1 just want to enter the month (March) is this possible?


  • #8
    zetor 4911
    Registered Users Posts: 421 ✭✭zetor 4911


    Seth Brundle wrote: »
    If the word "March" is in cell A1 then use the following formula... 
    =TEXT(EDATE(DATEVALUE("01-" & A1 & "-2020"),1),"mmmm")

    DATEVALUE("01-" & A1 & "-2020") allows me to convert the word to an actual date.
    EDATE allows me to add one month to our date
    TEXT allows me to format our new date in mmmm (full month name) format

    That worked for me many thanks to all for the suggestions.


Advertisement