Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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.
Avatar of HKFuey

ASKER

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
see this link

http://support.microsoft.com/?kbid=290152
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HKFuey

ASKER

Brilliant answer, thanks very much!

Andy