Excel Spreadsheet Help

[Concordedly]

Trying to build a tool for work. Thought you all might have more experience than me. I've manually adjusted the year for 2010 to accomodate for our system only using 30 days each month for the due date (with +1/-1 type offsets being added based on the due date to project the field "Next Due"). Is there a function in excel or an easier way through, maybe a coding language, that would be easier to accomodate this?

I'm not afraid to learn coding, just don't feel like going to the wrong place. If it is going to be a coding language, it will need to be Windows-friendly as that's what we use at my work and will need to have a friendly user interface that I can make or w/e. Meaning I don't want to write in C+ and have people using DOS screens. Kthx.

 

[Zatharus]

Would you please elaborate on what you are trying to ultimately accomplish? What is the goal of this tool?

 

[Concordedly]

Due Date Generator

Billing system uses 30 days to calculate bills. If due date falls on 31st, then due date becomes 1st.

Therefore:
31+30 days = 30th = Error
30 + 30 days on month that has 31 days = 29th = Error

So on and so forth.

End result of functionality needs to do the following:

Due date = 12-31-09
Next due = 01-01-10
Due date = 12-30-09
Next due = 01-30-09

We cannot have every user in my center enable the add-on pack. I also need to be able to, in another field, subtract 6 days from the "Next Due" when necessary to allow for "Payment Processing"

Thanks
Concordedly

 

[Zatharus]

OK. Let me rephrase it to make sure I understand what you want to do:

1. Input bill due date.
2. If bill due date is on the 30th of the current month, the bill due date gets adjusted to the 30th of the next month - 30 (or 31) days added on.
3. If bill due date is on the 31st of the current month, the bill due date is adjusted one day back to the first of the next month - only one day added on.

Does that sound right? I was a bit thrown off the the dates in your example.

Also, how do you handle February?

 

[Concordedly]

Correct.

I'll give the following examples.

Due Date: 12/27/09 Next Due: 01/27/10
Due Date: 12/30/09 Next Due: 01/30/10
Due Date: 12/31/09 Next Due: 01/31/10
Due Date: 01/28/10 Next Due: 02/28/10
Due Date: 01/29/10 Next Due: 03/01/10
Due Date: 01/30/10 Next Due: 03/01/10
Due Date: 01/31/10 Next Due: 03/01/10
Due Date: 02/01/10 Next Due: 03/01/10
Due Date: 02/02/10 Next Due: 03/02/10
Due Date: 03/30/10 Next Due: 04/30/10
Due Date: 03/31/10 Next Due: 04/01/10

So basically, for any due date that does not exist in the "Next due" it defaults to the 1st of the month. Otherwise it is the same date, just one month greater.

 

[Zatharus]

Ahhh...gotcha. Piece of cake!

I will explain how to use the DATE, DAY, MONTH, and YEAR functions in Excel:

=DATE(Year, Month, Day) 

=DAY(number)

=MONTH(number)

=YEAR(number)

The DATE function returns the date according to the three parameters entered: Year, Month, and Day. You can do math on any one of these parameters.

The DAY, MONTH, and YEAR functions require the serial date input. You won't need to worry about converting this as Excel stores all date data in serial format automatically - provided it is properly formatted.


EXAMPLE:

Say you enter the original Bill Due Date in cell A2 making sure to format that cell (and any other that will contain date information) to the date format of your choice:

Fig. 1 - Example

Fig. 2 - Date Format

Now in cell B2 we enter this nifty formula:

=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))

This formula runs the DATE function on the YEAR value returned from A2 (2009), the MONTH value returned from A2 (12) plus 1 (which adds a month to the value in date terms), and the DAY value in A2 (27).

Fig. 3 - Date code in action:

And, there you go! It is that simple and will work for all cases you specified. Take a look:

Fig. 4 - Ta-da!

I hope that helps you do what you wanted to accomplish. Does it all make sense?

Let me know if you need anything else.

 

[Concordedly]

It does format correctly, but is it the same function for everything. And, can you make a cell function that does: Next Due Date - 6 days. Definitely looks like I should investigate this function though, if nothing else, to save me time for other coding needs.

 

[Zatharus]

It does format correctly, but is it the same function for everything.

Yes. The formula I stated for you (as used in Fig. 3) was just copied down to the subsequent rows in that column. I then just entered in the rest of the example dates you see in Fig. 4 and it calculated everything as you see it. Try it out for yourself.

And, can you make a cell function that does: Next Due Date - 6 days.

Sure thing! I am not sure if you are asking for the original function minus six days or for a new function which is the original date plus six days. I will give you both. Feel free to do some experimentation on your own. This function is very easy to modify. I have bolded the minor modifications I made to give you the new versions.

Original Function: Next Due Date (+1 Month)

=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))

New Function: Original Function Minus Six Days

=DATE(YEAR(A2),MONTH(A2)+1,[B]DAY(A2)-6[/B])

New Function: Next Due Date (+6 Days)

=DATE(YEAR(A2),[B]MONTH(A2)[/B],[B]DAY(A2)+6[/B])

Let me know if that makes sense for you. Give those functions a try.

Definitely looks like I should investigate this function though, if nothing else, to save me time for other coding needs.

Manipulating date and time data in Excel is pretty straight forward. Take a look through this (very) basic tutorial if you would like some insight into other simple uses.

Let me know if you have any other questions.