Writing IF Statement for Date

Recommended Posts

I need help writing a calculation on how to get the summary of

"CURRENT", "OVER 30 DAYS", "OVER 60 DAYS", "OVER 90 DAYS", OVER 120 DAYS", and "TOTAL DUE". This is actually for a Statement i want to mail out to my customer every month.

This is what I came up with but it does not work:

If (Statement Today Date - Invoice Date ≤ 30; "Current"; If (Statement Today Date - Invoice Date ≤ 60; "Over 30 Days";If (Statement Today Date - Invoice Date ≤ 90; "Over 60 Days";If (Statement Today Date - Invoice Date ≤ 120; "Over 90 Days"; "Over 120 Days"))))

Share on other sites

Take a look here.

Share on other sites

i figure out a formula that works but now the only thing is i need to break down the summary amount by current, over 30 days.....

this is the equation i got to work to show which invoice is current, over 30 days, over 60 days...

Case(Statement Today Date <= (Invoice Date + 30); "Current"; Statement Today Date <= (Invoice Date + 60); "Over 30 days"; Statement Today Date <= (Invoice Date + 90); "Over 60 Days";"Over 90 Days")

Share on other sites

I apologize, but I do not follow what you are trying to achieve. Are you trying to create a report displays all the records that have balances grouped by their status categories?

Share on other sites

Yes...for example there are 5 total invoices.

1. invoice A - \$5.00 - current

2. Invoice B - \$3.00 - current

3. Invoice C - \$5.00 - over 30 days

4. Invoice D - \$5.00 - over 30 days

5. Invoice E - \$2.00 - over 60 days

my statement should indicate

current - \$8.00

over 30 days - \$10

over 60 days - \$2.00

TOTAL DUE - \$20.00

Edited by Guest

Share on other sites

What you want is a summary report grouped by the status field in conjunction with a summary field.

You want to take a quick look at this thread

Share on other sites

how do you write

Get (CurrentDate) in Filemaker 6.0

Share on other sites

Status(CurrentDate) or possibly Today

Share on other sites

thank you it work

another quick question

instead of using today date equation

Get (CurrentDate)

I would like to use a different date which i manually will add to the field what equation can i write to indicate to get the date i put in rather than todays date?

Share on other sites

Just put the field name in instead...

Share on other sites

I have this equation in FM8 and would like to also have it in FM6 but it is not working:

FM8

Case(Statement Today Date <= (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date <= (Invoice Date + 30);Sum ( Invoice Total ))

FM6

Case(Statement Date ≤ Date Invoiced + 60, Sum( Grand Total 2007 )) - Case(Statement Date ≤ Date Invoiced + 30, Sum( Grand Total 2007 ))

Any suggestion?

Share on other sites

Well... why not just use this:

Case( Statement Date <= DateInvoiced + 60 and Statement Date > Date Invoiced + 30 , Sum (Grand Total 2007) )

Share on other sites

Thank you for the reply...my Statement Date type field is a Date and the option is Indexed. why is the equation not working unless i change the field type to calculation which is Get (CurrentDate) ? sometime i can't get to the statement and i need the statement to be calculated from the date 04/15/07 instead of today date which is 04/24/07 because that will change my "over 30 days" "oer 60 days" etc. this is for FM8 and FM6

Edited by Guest

Share on other sites

1) A Date field is a field you enter dates into. Filemaker has a convenient option to automatically enter information based on other fields. That is how you've defined your field. It won't update correctly because it has no way of knowing that Today has changed. Today (or Get(CurrentDate)) won't change a Date (or Text, Number, etc) field with an auto-enter because FM doesn't 'check in' to see if the date has changed. You need a calculation, set to Unstored which will update upon many conditions.

2) Instead of using the expression Today, you can define an additional date field and set it to Global Storage. Then replace "Today" with the name of the field in the calculation. You can enter any date you want into that global field.

DJ

Edited by Guest

Share on other sites

help my caculation is not working

I have 3 fields to calcucate the total current or past due balance.

1. Field Name: Statement Current Type:Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); "Current")

2. Field Name: Statement Current Total Invoice Type: Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); Average ( Invoice Total ))

3. Field Name: Statement Current Summary Type: Summary

=Total of Statement Current Total Invoice

4. Filed Name: Statement over 30 days Type: Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); "Current"; Statement Today Date ≥ (Invoice Date + 60); "Over 30 days"; Statement Today Date ≥ (Invoice Date + 90); "Over 60 Days";"Over 90 Days")

5. Filed Name: Statement over 30 days Total InvoiceType: Calculation

Case(Statement Today Date ≥ (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date ≥ (Invoice Date + 30);Sum ( Invoice Total ))

6. 3. Field Name: Statement Over 30 days Summary Type: Summary

=Total of Statement over 30 days Total Invoice

any suggestion why its not working and why all my total is in the current field when some open invoices should be over 30 days late

Share on other sites

I have this equation in FM8 and would like to also have it in FM6 but it is not working:

FM8

Case(Statement Today Date <= (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date <= (Invoice Date + 30);Sum ( Invoice Total ))

FM6

Case( Statement Date <= DateInvoiced + 60 and Statement Date > Date Invoiced + 30 , Sum (Grand Total 2007) )

any suggestion???

Should I try "IF Statement??"

HELP.....

Edited by Guest

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• Who Viewed the Topic

2 members have viewed this topic:
WTM  ltwimberly
×
×
• Create New...