• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

DAYS360(start_date,end_date,method) - function in Access?

Hi.

I have a table with two date colums 'From' and 'To', with beginning and end dates, obviously. ;)

Is it possible to create a third column called 'Days', which calculates the number of days' difference between the 'From' and 'To' columns?

I read something in the help file about DAYS360(start_date,end_date,method), but isn't that an Excel function?

Thanks!
R;^)
0
DarkDarling
Asked:
DarkDarling
  • 6
  • 3
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi DarkDarling,

You can use the VBA function DateDiff to calculate the number of days between two dates,
e.g.

DateDiff("d", start_date, end_date)

See http://www.database-design-2000.com/dateDiff.htm for more information.

Yes, DAYS360 is an Excel (only) worksheet function that returns the number of days between two dates based on a 360-day year (twelve 30-day months).


BFN,

fp.
0
 
rockiroadsCommented:
DateDiff is what you use, like fanpages suggested

but I wouldnt recommend you create a days column, you will have to keep maintaining it each time you update the dates in the table and when u insert.

It is generally not good practice to put derved fields in the database

you should use it in your code, e.g. easy to use this function in a query as well,

0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Yes, thank you rockiroads, I meant to mention that!

There would be no need to store start, end, and days fields.

If you really did need to store a days value (perhaps to speed up processing or for immediate export to another data repository), you would only ever need to store either the start date, or the end date, not both, as you could calculate either date given the opposing date & the days difference!  [I hope that made sense]

BFN,

fp.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DarkDarlingAuthor Commented:
What I want it for is to create a chart showing energy use over periods of time... all I have are start and end dates, and thought it would be easier to create the chart from the number of days..... :)

R
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi DarkDarling,

Yes, that's true, but as the number of days can be derived from the start & end dates, there is little value to storing the actual days alongside the other columns in your database, unless, there is a great number of rows (records) that are static.

That is, if the records that already exist are not going to change once written to your table, and the time taken to process each record & calculate the days difference before you chart the values is excessive in terms of processing &/or user-wait time.


BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Thanks for the points by accepting my answer.

Were out combined answers not helpful enough?

I was curious why you graded as a 'B'.

BFN,

fp.
0
 
DarkDarlingAuthor Commented:
They half-helped. :)

I ended up finding the rest on my own.

But thanks!

R
0
 
[ fanpages ]IT Services ConsultantCommented:
:)  OK.

Which half?  Then I know which half to let you know next time.

BFN,

fp.
0
 
DarkDarlingAuthor Commented:
:)  You gave me the syntax...I figured out where to put it.

R
0
 
[ fanpages ]IT Services ConsultantCommented:
Ooh... expert yourself now, huh? ;)

Seriously, fair enough... we didn't get to the second half of your question before you graded it.

Have fun!

BFN,

fp.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now