Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Fixed Annual/Semi-Annual Bills

  1. #1

    Fixed Annual/Semi-Annual Bills



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear all,

    We have 2 fixed Billing cycles
    1-Annual on 07.February of each year (covers from 07.Feb Till 06.Jan of next year)
    2- Semi-Annual on 07.February (2 times a year From 07.Feb. Till next 06.Aug and then 07.August Till 06.Feb)
    am trying to find find formulas to get the FIRST Bill cycle date and LAST Bill Cycle date based upon start date of service and last date of service


    A B C D E F
    Service Name Start Date 1st Billed on End Date Last Billed on Bill Cycle Type
    DIA 05-Feb-2011 07-Feb-2011 25-Dec-2013 07-Feb-2012 Annual
    VPN 14-Jul-2009 07-Aug-2009 25-Apr-2014 07-Feb-2014 Semi-Annual

    Can we have these formulas in C and E, as am building other calculations based on these formulas?

    Appreciate your help,, It's OK if there's a way with VB and formulas did not workout

  2. #2
    What determines if it is annual or semi-annual?

  3. #3

  4. #4
    Quote Originally Posted by Bob Phillips View Post
    What determines if it is annual or semi-annual?
    Either to write it manually in column F, or to make each cycle in a different sheet

  5. #5
    Any ideas? I tried roundup formulas but all failed,,

  6. #6
    Hi, try this. I think I have it right.
    I'm just hoping that the 1st date in column E was actually mean't to be 7-Feb-13.
    Attached Files Attached Files

  7. #7
    I've added data validation drop-downs to Column F Just as a suggestion for you, might be quicker and save typo's.
    Attached Files Attached Files

  8. #8
    Beamer, You are Genius . It worked. Thank youuuuuuuuuuuuuuuuu

  9. #9
    Quote Originally Posted by Beamer View Post
    I've added data validation drop-downs to Column F Just as a suggestion for you, might be quicker and save typo's.
    Beamer, I'm sorry but the cycle for the Semi-Annual has changed but I got confused on how to change it in the formula, it has been changed to 07.Jan and 07. July instead of 07.Feb and 07.Aug. Can you please help?

  10. #10
    I don't blame you, I just had a look and totally forgot how I worked it out
    But it appears that the third "IF" statement in the formula is where it decides its something other then "Annual" in column F.
    So from that point on change any ",8,7" to ",7,7," and any ",2,7" to ",1,7"
    The changed numbers relate to:- 8 = Aug, 7 = Jul, 2 = Feb, 1 = Jan
    The "Date" function is in the format Year/Month/Day.

    I hope this works for you.

    Edit: I didn't change my formula and check the dates. I suggest checking the dates very close to the July date and if its out by a day you might need to change the 184 to 183...not sure
    Last edited by Beamer; 2014-12-09 at 03:51 PM.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •