+ Reply to Thread
Results 1 to 3 of 3

Formula to Select the Nth day of every month based on the value of a cell

  1. #1
    Forum Contributor
    Join Date
    08-13-2009
    Location
    Caribbean
    MS-Off Ver
    Excel 2003
    Posts
    129

    Formula to Select the Nth day of every month based on the value of a cell

    Hello All,

    I am looking to create a payment schedule with varying dates of the month.

    The normal payment cycle is as follows:

    1st payment due on date the invoice is raised, i.e., today.
    Recurring payments scheduled on an agree-upon date for an agree-upon number of months. First payment being due on the following month after the date of initial invoice.

    I have attached a spreadsheet to give a vague representation of what I am after.

    Basically I want a formula that will take whatever date i Have input under "payment date" and select that same date for the forthcoming months (number of months indicated under "Recurring payments") and input that date in the "Payment Dates" column.

    Any help is greatly appreciated!

    Thank you!!!!!
    Attached Files Attached Files

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

    Re: Formula to Select the Nth day of every month based on the value of a cell

    In E3 copied down
    =EDATE(E2,1)
    Is that what you wanted?
    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 Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Formula to Select the Nth day of every month based on the value of a cell

    I might be wrong, but it looks like you'll need a few conditions to cover you when your invoices change. I think if you copy the formula below into E3 and fill down through column E (and up to E2), you'll be able to ignore column E going forward. It'll pull the E column date from your other data, regardless of whether it's the first month or one of the subsequent payments.

    =IF(ISBLANK($D3),IF(ISBLANK($D2),DATE(YEAR($E2),MONTH($E2)+1,DAY($E2)),DATE(YEAR($E2),MONTH($E2)+1,DAY($D2))),$B3)

+ 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. [SOLVED] Formula to show first day of the month based on date in another cell
    By Marco-Kun in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-11-2016, 10:46 AM
  2. Replies: 5
    Last Post: 04-09-2014, 08:13 AM
  3. Formula to have excel select a date of the month based on certain criteria
    By dunkle83 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2014, 10:39 PM
  4. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2013, 08:45 AM
  5. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2013, 07:18 AM
  6. Select cell based on first of month date
    By mexcel300 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-16-2011, 06:15 PM
  7. Select cell formula based on criteria
    By roseberryv@orvis.com in forum Excel General
    Replies: 1
    Last Post: 06-15-2006, 07:10 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