Date calculation

[johnthompson]

We use a database to track subscribers to an annual service. Using two fields showing the date started and the date ended, I want to be able to calculate the number of days to the expiry date of the service (showing a minus value if the expiry date has passed). I tried the following, but obviously this fails when the start and end dates span end of year (ie start 4/12/02 end 4/12/03)

DayofYear(Ad ended) - DayofYear(Today)

Anyone got the solution?

[LiveOak]

Days (calculation, number) = Ad ended - Status(CurrentDate)

Subtracting two dates always gives a number result in days. Unlike "Today", Status(CurentDate) doesn't recalculate every time you open the file, only when needed.

-bd

[johnthompson]

Thanks for the rapid response. Unfortunately I am still getting incorrect results even using your formula. The Ad ended field is a date field and the "days to go" field is a number field. Yet I have a record showing an ad end of tomorrow's date but the number 7 showing in the days to go field!

[BobWeaver]

You need to make the calculation field unstored.