How to get date to auto change for bills

**skullzy** · 11-25-2009, 02:39 PM

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.

**JBeaucaire** · 11-25-2009, 03:21 PM

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.

**skullzy** · 11-26-2009, 07:13 AM

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

**SDruley** · 11-26-2009, 09:44 AM

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.

**skullzy** · 11-26-2009, 10:57 AM

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.

**skullzy** · 11-29-2009, 04:55 PM

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