locked
calculate days between dates RRS feed

  • Question

  • I would like to have a field in a report that counts the days between two dates.  I've got a DateRcvd field and a DateComplete field.  Both date fields have the date format like 3/23/2011 (short date).  What I'm looking for is a calculation that would show for a record that has DateRcvd of 3/23/2011 and DateComplete fo 3/29/2011 answered as 6 (number of days between the two dates).  Some DateComplete fields might not have any date in it because the project might not be complete. 

    In relation to the above info, I'd also like to have another field in the report that would show the average number days it takes to complete all the projects that have DateComplete dates in the field.  Help please!

     


    TAK
    Thursday, May 12, 2011 5:25 PM

Answers

  • You can use theDateDiff() function for this;

     

    DateDiff("d",[DateComplete],[DateRcvd])

     

    EDIT: for the average days;

    SELECT Avg(DateDiff("d",[DateComplete],[DateRcvd])) AS AvgCompletion
    FROM tblYourTableName
    HAVING [DateComplete] Is Not Null

     

    HTH,
    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by taking Friday, May 13, 2011 6:30 PM
    Thursday, May 12, 2011 5:42 PM
  • Use the DateDiff function: DateDiff("d", [DateRcvd], [DateComplete])

    If you want 0 (or a fixed value) for incomplete ones, use

    IIf(IsNull([DateComplete]), 0, DateDiff("d", [DateRcvd], [DateComplete]))

    If you want to treat DateComplete as today's date if it's incomplete, use

    DateDiff("d", [DateRcvd], Nz([DateComplete], Date()))

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by taking Friday, May 13, 2011 6:30 PM
    Thursday, May 12, 2011 5:45 PM

All replies

  • You can use theDateDiff() function for this;

     

    DateDiff("d",[DateComplete],[DateRcvd])

     

    EDIT: for the average days;

    SELECT Avg(DateDiff("d",[DateComplete],[DateRcvd])) AS AvgCompletion
    FROM tblYourTableName
    HAVING [DateComplete] Is Not Null

     

    HTH,
    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by taking Friday, May 13, 2011 6:30 PM
    Thursday, May 12, 2011 5:42 PM
  • Days_To_Complete: DateDiff("d", [DateRcvd], IIF([DateComplete] Is Null, Date(), [DateComplete]))+1
    Thursday, May 12, 2011 5:43 PM
  • Use the DateDiff function: DateDiff("d", [DateRcvd], [DateComplete])

    If you want 0 (or a fixed value) for incomplete ones, use

    IIf(IsNull([DateComplete]), 0, DateDiff("d", [DateRcvd], [DateComplete]))

    If you want to treat DateComplete as today's date if it's incomplete, use

    DateDiff("d", [DateRcvd], Nz([DateComplete], Date()))

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by taking Friday, May 13, 2011 6:30 PM
    Thursday, May 12, 2011 5:45 PM
  • What would the expression be if I just want to ignore the datecomplete record if there is nothing in the datecomplete field?  Say I've got 7 records of a company and 5 have a datecomplete entered but 2 do not.  What would the expression be to exclude the 2 with nothing in the datecomplete field?  

    Also, is there something that could be added to identify if there is no datecomplete entry for any records of a company.  Like if no records for a specific company have any datecomplete entries, it would display "No Dates Entered" or "0"  or something like that?

    My report is just summaries of each companies activity so the detail of the records don't display, only summary totals.


    TAK
    Thursday, May 12, 2011 6:20 PM
  • Use a query for the report's RecordSource. To ignore records with no DateComplete, set the Where clause to WHERE DateComplete IS NOT NULL. To only see those records with no DateComplete, set the Where clause to WHERE DateComplete IS NULL.

    (If you're not comfortable working directly with the SQL, set a criterion of IS NOT NULL or IS NULL under the DateComplete field in the query builder)


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, May 12, 2011 6:29 PM
  • Doug, I do like the expression you have to treat DateComplete as today's date (and it works) but one of my companies only has one record and the datecomplete for it is empty and the value generated is -657406.  The rest of the companies have correct values in it.  Any reason for this strange value?

    DateDiff("d", [DateRcvd], Nz([DateComplete], Date()))


    TAK
    Thursday, May 12, 2011 7:24 PM
  • What's the value of DateRcvd? By any chance is it erroneously set to June 12, 211 instead of June 12, 2011?

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, May 12, 2011 7:52 PM
  • You are correct.  Right after I made the above post I checked and the datecomplete was entered wrong, way wrong.
    TAK
    Thursday, May 12, 2011 8:31 PM