Access 2003, calculating dates

15 pts.
Tags:
Access 2003
calculating dates
I have a database and I need the following to calculate based off a date field. My problem is that regardless if the date entered in that field is the beginning, middle, or end of month, I need another field that will produce a due date, which has to be the end of that month, which could be 6 month, plus some days. See example below. DateReceived = existing field 5/2/08 DueRO = calculated field 10/2/08 (five months from DateReceived) ReportDueFFC = calculated field 11/30/08 (must equal 6 months, plus whatever amount of days necessary to generate the end of month) DaysLate = calculated field (equal number of days past due, using the ReportDueFFC field) I have no problem calculating the number of days, only the ReportDueFCC field. I am not sure how I can generate a date, that vary from DueRO, because that field depends on the DateReceived field. If you have any suggestion, I would greatly appreciate it. Thanks in Advance.
ASKED: April 8, 2009  10:27 PM
UPDATED: April 13, 2009  3:34 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

These are the formulas where B1 is the DateReceived field and the rest are one row below.:

DateReceived: 5/2/08
DueRO: =DATE(YEAR(B1),MONTH(B1)+5,DAY(B1))
ReportDueFFC: =DATE(YEAR(B2),2+MONTH(B2),1)-1
DaysLate: =B3-NOW()
———-
DueRO: =DateAdd(“m”;5;[DateReceived])
ReportDueFFC: =DateAdd(“d”;-Day(DateAdd(“m”;1;DateAdd(“m”;6;[DateReceived])));DateAdd(“m”;1;DateAdd(“m”;6;[dateReceived])))
DaysLate: =DateDiff(“d”;[ReportDueFFC];Date())

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Helen1234
    Is this formula for Microsoft Access?
    15 pointsBadges:
    report
  • mshen
    Sorry this is for excel. You can import excel spreadsheets to Access, but I'm not completely sure if it will save the formulas.
    27,385 pointsBadges:
    report
  • mshen
    You can create linked tables to an excel worksheet and update your dates in the worksheet.
    27,385 pointsBadges:
    report
  • SbElectric
    Mshen has provided good information. The only comments I have for you: 1) Do you need exact 5 months for DueRO? It is fine for 5/2/08. But if it is 1/31/08? Adding 5 months will give 6/31/08?? Similar situation for February. 2) This is Excel function (Date). DateAdd or DateDiff are not in Excel 2007. But you may write similar code in Access using VB code. Or as Mshen suggested you can test in Excel & then use Access to link the excel worksheet. 3) The trick to get the last day of a month is to define the 1st day of next month & then subtract 1 day. Excel (and VB) will automatically adjust the date. Please keep us posted with your approach. Good luck.
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following