+ Reply to Thread
Results 1 to 8 of 8

Semi Monthly Dating

  1. #1
    Registered User
    Join Date
    07-13-2011
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Semi Monthly Dating

    I am creating an amortization schedule for a Canadian mortgage.

    Attached is a copy of the spreadsheet.

    I want to have dates displayed for payments. Annual, semi-annual, quarterly, monthly, semi-monthly, biweekly and weekly. All of the dating except for semi-monthly has gone smooth. I even tried to put a list of dates in column A (and hide them with white text) and use that for my date display in Column C. If anyone can help me either reconstruct my IF statement in Column C so that the Semi Monthly dates will appear or be able to generate a listing of semi-monthly dates in Column A, I would greatly appreciate it.

    I want to be able to enter ANY date in Cell O8 (example 3/4/2011). The next date could be 3/18/2011 or 3/19/2011 (it doesn't matter to me if it goes up by 14 or 15 days). Then, the days of the month stay constant. So in other words, the dates would go:
    3/4/2011
    3/18/2011
    4/4/2011
    4/18/2011
    5/4/2011
    5/18/2011

    I have found several spreadsheets/tips online that take the inputted date and force the payments to be at the beginning and middle or middle and end of the month. I'd rather not go down that road if I could help it.

    Thanks so much for any help given
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Semi Monthly Dating

    In C5:
    =C4+14

    In C6 downwards:
    =DATE(YEAR(C4),MONTH(C4)+1,DAY(C4))

    ?

  3. #3
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Semi Monthly Dating

    Hi

    In order to keep the formula manageable, I would go with the "hidden" values in column A.

    In A4
    =C4
    In A5 and copied down
    =IF(MOD(ROW(),2)=0,DATE(YEAR(C4),MONTH(C4)+1,1),A4+14)
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  4. #4
    Registered User
    Join Date
    07-13-2011
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Semi Monthly Dating

    Thanks for the reply guys.

    However, both of those suggestions skip months. In other words, they'll do two payments in month 2 and then do two payments in month 4.

    Still looking for some help - appreciate any feedback!

    Thanks!

  5. #5
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Semi Monthly Dating

    Quote Originally Posted by Mathalete View Post
    Thanks for the reply guys.

    However, both of those suggestions skip months. In other words, they'll do two payments in month 2 and then do two payments in month 4.

    Still looking for some help - appreciate any feedback!

    Thanks!
    Hi
    I beg to differ.
    If you look at the attached file, with the formulae I provided, you will see that it does do exactly what you asked, with 2 payments every month, on the 1st and 15th
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Semi Monthly Dating

    Yeah, both mine and Roger's solutions work fine, not sure what you're doing wrong?

  7. #7
    Registered User
    Join Date
    06-21-2011
    Location
    Dhaka
    MS-Off Ver
    Excel 2003, 2007
    Posts
    20

    Re: Semi Monthly Dating

    Type the Date 3/4/2011in C4
    Copy the formula in C5
    =DATE(YEAR(C4),MONTH(C4),DAY(C4)+14)

    Apply this logic

  8. #8
    Registered User
    Join Date
    07-13-2011
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Semi Monthly Dating

    Yes! I was doing something wrong indeed.

    Thanks again for the wonderful help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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