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.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 8, 2009 10:27 PM
UPDATED:
April 13, 2009 3:34 AM
Answer Wiki:
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())
To see all answers submitted to the Answer Wiki: View Answer History.
Is this formula for Microsoft Access?
Sorry this is for excel. You can import excel spreadsheets to Access, but I’m not completely sure if it will save the formulas.
You can create linked tables to an excel worksheet and update your dates in the worksheet.
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.