Better way to subtract dates from ea other?

[surfer]

Hello-

I am trying to put together a calculation that will determine how many days it has been since an invoice has been submitted. I tried the following calculation:

DayOfYear ( Get ( CurrentDate ) ) - DayOfYear ( DATE SUBMITTED )

The problem is that it fails when the dates span 2 calendar years.

Is there a better way to write this that will give an exact number of days?

Also is there a way to display the answer in "X months and Y days".

Any help would be greatly apprecitated.

Thank you.

[RalphL]

Date Submitted - Get ( CurrentDate) will give you the number of days. After this it gets messy. Months can 28 to 31 days, years can be 365 or 366 days. I had an e-mail from OneStopFileMaker.com that offers "free" custom functions including one to do this. Haven't checked it.

[surfer]

One last thing, I have a field called: Invoice PAID whose values are either "Yes" or "No"

is there a way to STOP the calculation of days since submitted once Invoice PAID = Yes :

Thank you very much...

[jrb12]

Surfer,

To get the number of days dependent on PAID:

If(PAID = "No", Get( CurrentDate ) - DATE SUBMITTED, "")

For the number of months, subtract the month fields and add 12 times the difference in the years:

Month (Get( CurrentDate )) - Month(DATE SUBMITTED) + 12 * (Year( Get( CurrentDate)) - Year(DATE SUBMITTED))

and the number of days left over is:

Day(Get( CurrentDate)) - Day(DATE SUBMITTED)

Cheers,

John.

[-Queue-]

Make the field a number with an auto-enter calculation of

Case( Invoice PAID = "No"; DayOfYear ( Get ( CurrentDate ) ) - DayOfYear ( DATE SUBMITTED ); DaysCalc )

where DaysCalc is the name of the field with the auto-enter calc, i.e. *this* field. Click OK and then deselect the 'Do not replace existing value for field' option.

[-Queue-]

For X Months and Y Days,

( Month( Get ( CurrentDate ) ) - Month( DATE SUBMITTED ) - ( Day( Get ( CurrentDate ) ) < Day( DATE SUBMITTED ) ) ) + (Year( Get ( CurrentDate ) ) - Year( DATE SUBMITTED )) * 12 & " Month" & Left( "s"; ( Month( Get ( CurrentDate ) ) - Month( DATE SUBMITTED ) - ( Day( Get ( CurrentDate ) ) < Day( DATE SUBMITTED ) ) ) + (Year( Get ( CurrentDate ) ) - Year( DATE SUBMITTED )) * 12 <> 1 ) & " and " & Case( Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ) >= 0; Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ); Date( Month( DATE SUBMITTED ) + 1; Day( DATE SUBMITTED ); Year( Get ( CurrentDate ) ) ) - Date( Month( DATE SUBMITTED ); Day(DATE SUBMITTED ); Year( Get ( CurrentDate ) ) ) + Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ) ) & " Day" & Left( "s"; Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ) <> 1 )

This assumes that a 'month' is equal to the difference of two consecutive months with the same Day, e.g. 5/7 to 6/7.

[-Queue-]

Here's a slightly modified version that seems to work more accurately.

Let( m = ( Month( Get ( CurrentDate ) ) - Month( DATE SUBMITTED ) - ( Day( Get ( CurrentDate ) ) < Day( DATE SUBMITTED ) ) ) + (Year( Get ( CurrentDate ) ) - Year( DATE SUBMITTED )) * 12 ; m & " Month" & Left( "s"; m <> 1 ) ) & " and " & Case( Day( Get ( CurrentDate ) ) >= Day( DATE SUBMITTED ); Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ); Date( Month( DATE SUBMITTED ) + 1; Day( Get ( CurrentDate ) ); Year( Get ( CurrentDate ) ) ) - Date( Month( DATE SUBMITTED ); Day( DATE SUBMITTED ); Year( Get ( CurrentDate ) ) ) ) & " Day" & Left( "s"; Day( Get ( CurrentDate ) ) - Day( DATE SUBMITTED ) <> 1 )

[surfer]

Thank you!

That works brilliantly!

I really appreciate your help...