Solved

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

Posted on 2004-04-27
10
578 Views
Last Modified: 2007-12-19
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
Comment
Question by:DarkDarling
  • 6
  • 3
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 

Author Comment

by:DarkDarling
Comment Utility
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 125 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 

Author Comment

by:DarkDarling
Comment Utility
They half-helped. :)

I ended up finding the rest on my own.

But thanks!

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:)  OK.

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

BFN,

fp.
0
 

Author Comment

by:DarkDarling
Comment Utility
:)  You gave me the syntax...I figured out where to put it.

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now