+ Reply to Thread
Results 1 to 14 of 14

Excel formula if month passed or running then the month name else blank

  1. #1
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Excel formula if month passed or running then the month name else blank

    Dear Expert,

    Is it possible to get the result as month if month has been passed from Jan to Dec then the month name or else result will be blank. For e.g. if current running month is October then the list by using the formula will give result from Jan to Oct and for rest two month results will be blank.

    Please do let me know if needs further clarification.

    Thank you for your valuable support.

    Regards,

    Neilesh

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    By now Neilesh.... you should know that a sample sheet is far more use than a vague description.
    Glenn



  3. #3
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Dear Experts,

    Please find here attached sample workbook where if the Oct month is running then the formula will give the results from Jan till Oct and if rest two months Nov and Dec is yet to come then the results will be blank.

    Regards,

    Neilesh
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    Hi, try this:

    =IF(MONTH(TODAY())>=A2,TEXT(DATE(2018,A2,1),"mmm"),"")

    There is no need to adjust the 2018 when the year changes.

  5. #5
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Dear Expert, Thank you for your support expected results are giving the perfect from Jan to Oct but the Nov and Dec for Cell B12 and B13 expecting as blank which are giving output as Nov and Dec. As per message # 1, if the current month if October then the results are expecting months name from Jan to Oct and rest two other months Nov and Dec as blank.

  6. #6
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Dear Expert Glenn, Thank you so much i have by changing the month in control panel and the output is giving perfect. Thank you once again.

  7. #7
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    since we are in December month so the output is giving from Jan to Dec

  8. #8
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Dear Expert, in the provided formula =IF(MONTH(TODAY())>=A2,TEXT(DATE(2018,A2,1),"mmm"),""), if i do change the year from 2018 to 2019 then the output is giving for all the 12 months. Can you please look into the same.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    When you say"if i do change the year from 2018 to 2019" where? In the formula? In the PC settings, and if so, which month have you selected?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    I think you mean you changed the year in the PC settings, but left the month as December, so it will give all 12...

  11. #11
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Is there any chances Sir the without changing the PC Clock if i do change the year in the formula from 2018 to 2019 then the output will be blank.

    Please find here attached revised sample workbook with the year dropdown. If i do change the year from the dropdown then the passed and current month name will be appeared but the year is not arrived then blank.

    Thank you for your valuable support.
    Attached Files Attached Files

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    ???
    In B2, copied down:
    =IF($F$2>YEAR(TODAY()),"",IF(MONTH(TODAY())>=A2,TEXT(DATE(2018,A2,1),"mmm"),""))

    The 2018 in the formula does NOTHING other than help the formula produce Jan as the result rather than the month number.

    It will work just as well with your year of birth, or the year of India.s Independence....

  13. #13
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    747

    Re: Excel formula if month passed or running then the month name else blank

    Perfect Solution Sir. Thank you so much

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,978

    Re: Excel formula if month passed or running then the month name else blank

    Another version, avoiding the 2018 in the formula might be:

    =IF($F$2>YEAR(TODAY()),"",IF(MONTH(TODAY())>=A2,LOOKUP(A2,ROW($1:$12),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}),""))

+ 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. If formula to get pivot data only when full month has passed
    By davidharper2005 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2018, 06:59 AM
  2. get nearest month/day/year that hasn't passed from two digit number with formula
    By 951Michael in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2017, 02:08 PM
  3. Replies: 2
    Last Post: 08-16-2017, 02:11 AM
  4. Formula to show how many days have passed since the last day of the month
    By baby_gizmo87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2014, 09:03 AM
  5. [SOLVED] Counting & summing formula (until last month,this month,until this month..
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2014, 01:14 PM
  6. Excel 2007 : Running Month to Month totals for end of year
    By Josephb1976 in forum Excel General
    Replies: 0
    Last Post: 07-29-2011, 04:54 PM
  7. Replies: 6
    Last Post: 01-09-2006, 04:55 AM

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