+ Reply to Thread
Results 1 to 9 of 9

formula for recurring Nth day of the month

  1. #1
    Registered User
    Join Date
    08-25-2015
    Location
    Kolkata, India
    MS-Off Ver
    2013
    Posts
    59

    formula for recurring Nth day of the month

    I have certain date in certain cell. I need a formula that can automatically change the month when the month changes. E.g. I have entered 20th Apr. 2017 in a cell and I want that this 20th Apr. 2017 becomes 20th May, 2017 when May starts. The formula should not have to refer to any kind of reference cell/range. It should be only a formula that I could put in a cell for Nth day of the month.

    Thanks
    Nadim

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,624

    Re: formula for recurring Nth day of the month

    Try this:

    =DATE(YEAR(TODAY()),MONTH(TODAY()),20)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,624

    Re: formula for recurring Nth day of the month

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,899

    Re: formula for recurring Nth day of the month

    One way:

    =--TEXT(TODAY(),"yyyy/mm/\2\0")

  5. #5
    Registered User
    Join Date
    08-25-2015
    Location
    Kolkata, India
    MS-Off Ver
    2013
    Posts
    59

    Re: formula for recurring Nth day of the month

    Dear all,

    Thank you so much for your guidance.

    Have a nice day.

    Thanks
    Nadim

  6. #6
    Registered User
    Join Date
    08-25-2015
    Location
    Kolkata, India
    MS-Off Ver
    2013
    Posts
    59

    Re: formula for recurring Nth day of the month

    What would be the formula a) if I want to change the date Yearly instead monthly and b) if I want to change the date after every 15 days from a given date, say 11th Apr. 2017?

    Thanks
    Nadim
    Last edited by nadimqaisar; 04-20-2017 at 08:38 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,624

    Re: formula for recurring Nth day of the month

    The formula I gave you can be adapted. Let's say you always want 20 April and just change the year, use this:

    =DATE(YEAR(TODAY()),4,20)

    If you want to change the date every 15 days, then maybe this:

    =DATE(YEAR(TODAY()),MONTH(TODAY()),A1)

    where A1 contains the number (e.g. 15) that you want to change the date to.

    It's all quite flexible - open a new workbook and have a play with various options. Don't be afraid!

  8. #8
    Registered User
    Join Date
    08-25-2015
    Location
    Kolkata, India
    MS-Off Ver
    2013
    Posts
    59

    Re: formula for recurring Nth day of the month

    Thank you so much Ali

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,624

    Re: formula for recurring Nth day of the month

    You're welcome!

+ 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. 12 month recurring income formula
    By JCBlake in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2014, 05:49 AM
  2. [SOLVED] compare recurring item between month
    By koi in forum Excel General
    Replies: 10
    Last Post: 11-17-2012, 10:33 AM
  3. Replies: 6
    Last Post: 09-06-2005, 03:05 AM
  4. [SOLVED] Calculating recurring date in following month, calculating # days in that period
    By Walterius in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Calculating recurring date in following month, calculating # days in that period
    By Walterius in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 12:05 AM
  6. [SOLVED] Calculating recurring date in following month, calculating # days in that period
    By Walterius in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2005, 11:05 PM
  7. Replies: 6
    Last Post: 06-04-2005, 07: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