# Better way to subtract dates from ea other?

This topic is 6436 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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.

##### Share on other sites

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.

##### Share on other sites

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...

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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 )

##### Share on other sites

Thank you!

That works brilliantly!

##### Share on other sites

This topic is 6436 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account

• ### Similar Content

• #### Recovery and reopening of FM7 files

By Tony Morosco,

• 2,573 views
• #### Filemaker 6 for Mac issue.

By Tumma K,

• (and 4 more)

Tagged with:

• 8 replies
• 3,398 views
• #### FM3 upgrade to FM Pro 5.5 - Windows 8 installation possible?

By MrEddByrnes,

• (and 4 more)

Tagged with:

• 4,140 views
• #### Moving data from fp5 to fp13

By bmill,

• (and 4 more)

Tagged with:

• 2 replies
• 2,735 views
• #### Filemaker 5.5 database will not delete records on-line

By randyinla,

• (and 4 more)

Tagged with: