Solved

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

Posted on 2004-04-27
10
589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10935747
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
ID: 10935815
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 ]
ID: 10935917
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:DarkDarling
ID: 10935963
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
ID: 10936000
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936091
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
ID: 10936158
They half-helped. :)

I ended up finding the rest on my own.

But thanks!

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936214
:)  OK.

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

BFN,

fp.
0
 

Author Comment

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

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936339
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

688 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