There are total 5 fields about Date in Project Access Database:



1) Open Date
2) Due Date
3) Extension Date
4) Close Date
5) Days Late

Requirement:

Due date = Open Date + 25 working days
Days Late = today minus "Due Date" if "Extension Date" is blank, or today minus "Extension date" if "Extension Date" is not blank. If today minus the date>0, show actual number, otherwise show " On Track"

It looks simple, but it is very challenging to me.


It requires number or word on [Day Late] field.

When [ExtensionDate] is blank:
if today is later than [DueDate], [DayLate]=(today - [DueDate]),
If [today is earlier than [DueDate] [DayLate] shows "ON Track"

When [ExtensionDate] is not blank:
If Today is later than [DueDae], [DayLate]= (Today -[DueDate]),
If Today is earlier than [DueDate], [DayLate] = " ON Track"

[DueDate]=([OpenDate]+25 business days)



Many thanks !