HKFuey

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?

Invoice Date-Date()+SetDays=NoOfDa

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?

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

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

numberofdays=dateDiff("d",

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

?dateDiff("d",#11/14/2012#

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Brilliant answer, thanks very much!

Andy

Andy

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.