+ Reply to Thread
Results 1 to 6 of 6

How to get date to auto change for bills

  1. #1
    Registered User
    Join Date
    11-25-2009
    Location
    belfast
    MS-Off Ver
    Excel 2007
    Posts
    4

    Exclamation How to get date to auto change for bills

    hey,
    I don't know if this has a name and i've tried searching using google and this site so i appologies if this has already been brought up.

    i have my own billing system for a catalogue that i own and i have a part that shows me my "Previous, Current, Next" bills due dates but in order for this to stay up to date i have to manualy enter the previous bills date.

    example;
    Previous bill [ 21 november 2009 ]
    Current bill [ 19 december 2009 ]
    Next bill [16 january 2010 ]

    in code view this would look like this

    [G3] Previous bill [ 21 november 2009 ]
    [H3] Current bill [ =SUM(G3+28) ]
    [I3] Next bill [ =SUM(H3+28) ]

    my bill is payed every four weeks (28days), but in order for this to stay up to date i have to update it manualy and i was woundering if there was a way round this so that it would show the 3 dates (Past, Present, Future) but automaticaly update it'self.

    also before i forget am using Excel 2007.

    thanks in advance.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to get date to auto change for bills

    Hi Skullzy, welcome to the forum.

    1) You should update your profile so that it shows Excel 2007 instead of 2003 if that's what you're using.

    2) Is a VBA solution ok?

    A macro could check the date(s) in the "Current" column and any dates that have passed, the value from that cell could be copied into the "Previous" column...causing the CURRENT and NEXT dates to adjust by the formulas already there.

    A Workbook_Open even could take care of it for you.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-25-2009
    Location
    belfast
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to get date to auto change for bills

    thanks for the reply,
    i thought i did change it to 2007 sorry about that.

    okay am... When it comes to Macro that's where i become a complete and utter amateur. Although I once looked into Macro but i never assumed that i would need to use it to do what i needed.

    at the moment am not on my home pc but am assuming macro's already installed.

    just out of curiosity what is a workbook_open ? i googled it and it came up code is this something that you would use in a macro or VBA

    would you be able to recommend tutorials for me to fallow for what i need to do? although i don't expect anyone to do the work required for me and once i get home i'll search for tutorials myself but if you know any tutorials on how to use macro and excel to do what i need or that can help. it would be appreciated.

    what would really be appreciated is if someone could do the work for me :P lol.

    but ill take what i can get

    thanks
    Last edited by skullzy; 11-26-2009 at 07:20 AM. Reason: mistakes

  4. #4
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Red face Re: How to get date to auto change for bills

    Since you are so new to Excel, perhaps there is a worksheet solution for you. JBeaucaire's solution is the most efficient but if you could send a stripped down example of your spreadsheet we may be able to help you in terms that you can more clearly understand.

  5. #5
    Registered User
    Join Date
    11-25-2009
    Location
    belfast
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to get date to auto change for bills

    Am not sure how i could strip it down for you as there isn't really any coding to it, its just the basic spreadsheet

    But I have 8 spreadsheets in one document

    Sheet1 is named INFO it contains the basic information i need. To allow me to keep track of my customers.

    Sheet2-8 is customers

    Here is a link showing you my INFO SHEET

    At the top right i've highlighted the due dates for my bills displaying the Last Next and Later dates.

    The Last date is just as it looks, The Next date is =SUM(G3+28), The Later date is =SUM(H3+28)

    so.
    21 November 2009 + 28 days = 19 December 2009
    19 December 2009 + 28 days = 16 January 2009

    I've also got the current date displayed just above the due dates.

    i tried solving this myself before finding this forum i thought if i done something like.

    IF (CURRENT DATE) WAS GREATER THAN OR EQUAL TO (NEXT BILL) then change (LAST BILL) to (NEXT BILL).

    but no mater what way i did it i couldn't get it to display without looping and for some reason excel hates looping lol.

    when you said about send a "stripped down example" the only thing that would be left would be the due dates which is just a simple =SUM() function. but if you still want that ill be more than happy to attach it.

  6. #6
    Registered User
    Join Date
    11-25-2009
    Location
    belfast
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: [SOLVED] How to get date to auto change for bills

    It's not exactly what I wanted but it will help me just the same

    I used the fallowing functions

    =MONTH(), =YEAR(), =NOW(), =CHOOSE(), =IF()

    I added all my due dates for the next 2 years onto a work sheet

    E1 has the fallowing code =MONTH( NOW() ) to display the month example: 11 = November

    F1 has the year 2009 which is manually changed, G1 has 2010 which is also manually changed this will help when it come to the =IF function.

    Instead of me having to manually change the date every month I now only now have to change the it once every 2 years.

    A1 to A12 are the dates for 2009, 2011

    C1 to C12 are the dates for 2010

    With the dates lined up I use the function =CHOOSE to select the dates

    2009 cell F2
    =CHOOSE( MONTH( NOW() ), A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12)

    2010 cell G2
    =CHOOSE(MONTH( NOW() ), C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12)

    The Last due date Cell E2
    =IF(YEAR(NOW())=F1, F2, IF(YEAR(NOW())=G1, G2, "error! End"))

    Basically
    IF (CURRENT YEAR) = CELL F1 (2009) then display bill date for 2009 ELSE IF (CURRENT YEAR) = CELL G1 (2010) then display bill date for 2010.

    I did have some problems when it came to some dates because in one of the months each year my bill is paid twice example the dates 03/07/2010 – 31/07/2010 which would have messed up the =CHOOSE function because it’s choosing the dates based on the month.

    B7 = 31/07/2010

    =IF( NOW()>=B7, "31/07/2010 ", "03/07/2010 ") so I had to do this basically if the current date is Greater than or Equal to the 31/07/2010 then display that else display previous date.

    I tried to get that above code to work like =IF( NOW()>=”31/07/2010”, "31/07/2010 ", "03/07/2010 ") but no matter what I done it wouldn’t work so I had to add the date into a field.

    But thanks for the help you guys provided

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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