Jump to content
Sign in to follow this  
maiton.vang

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 this post


Link to post
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")

please help i need to get the invoice amount on the right column

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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

summary report thread

Share this post


Link to post
Share on other sites

Status(CurrentDate) or possibly Today

Share this post


Link to post
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 this post


Link to post
Share on other sites

Just put the field name in instead...

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.