# Access calculate number of days

I have a table of invoice terms and I want to calculate how many days in the future an Invoice is due from the Invoice Due Date. Some terms are set days so I'm assuming:

Invoice Date-Date()+SetDays=NoOfDays until Due e.g. 10th Nov + 20 = 16 Days

The ones I'm having trouble with have data like this: -
Months: e.g. 2
DayOfMonth: e.g. 31
This means the customer pays the invoice in 2 months time on day 31 of the month after the invoice date. (So an Invoice with a date of 14th Nov is due 31st Jan)

Does anyone know how to calculate number of days from the invoice due date?
Jim Dettman (EE MVE)

Your going to need to write a procedure if you have different calculations and pass an argument that indicates the type of formula (ie. add days, Day of Month, etc), how much you want to add, and the date to start with.

As for the actual calculation, that's easily done with DateDiff(), which can take a date, a period (days, weeks, months, etc), and the number of, and return the date.

Enough to get you started or do you need more?

Jim.

Hi Jim,

Hi Jim,
I have a flag in the terms table so I can do an if statement to separate the 2 types.

If the data is: -
Invoice date = 10th Nov
Months allowed = 2
Payment Day = 31

How do I format datediff() to give me 16*+31**+31***=78
*Number of days left this month
** Plus the number of days in December
*** Plus the day of month to pay
using the DateDiff() function posted by jim

numberofdays=dateDiff("d",#11/14/2012#,#1/31/2013#)

you can try this in the immediate window of the vba window

?dateDiff("d",#11/14/2012#,#1/31/2013#)
78

if you want to consider excluding weekends and holidays to get the DueDate of payment

http://support.microsoft.com/?kbid=290152