Date calculation fun...not!

[Rich S]

I'm having the same trouble now that I did 30 years ago: trying to get a date. :S

If you'll open the attached file, go to the Scheduled Checks layout, then record 5, you'll see the problem in the Year-Months-Days columns. (They all use the date in the yellowed Due Date column for their calculations.) Days that are past due are in red (which is what I want).

However...

Here's the problem: for the 11.16.06 due date, it's really only 2 months, 17 days away. However, as coded into the calculation, FM is saying, "No you don't have 2 months + 17 days left, but 3 months -14 days left." *heavy sigh* You can't argue with logic like that...but I'd rather have the 2 + 17 instead of the 3 - 14 numbers appear.

I've tried a number of different attacks at the math, but all I keep coming up with is Einstein's long-sought-after Universal Gravitational formula instead of a simple fix for the date fields. Hmmph!

As always, your help is most appreciated.

Ciao,

Rich

Andy_Database.zip

Edited August 30, 2006 by Guest

[John Mark Osborne]

This formula will do what you want and return the results in a single field. If you want it in separate fields, you can easily break the formula apart where you see the ampersands (&).

--- BEGIN ---

Let(

[Current = Get(CurrentDate);

DueDate = SC_Due_Date1];

GetAsNumber(Year(DueDate) -

Year(Current) - Case(DueDate <

Date(Month(Current); Day(Current); Year(DueDate)); 1; 0)) & " Years, "

&

GetAsNumber(Mod(Month(DueDate) -

Month(Current) + 12 - Case(Day(DueDate) <

Day(Current); 1; 0); 12)) & " Months, "

&

GetAsNumber(Day(DueDate) -

Day(Current) + Case(Day(DueDate) >=

Day(Current); 0; Day(DueDate -

Day(DueDate)) <

Day(Current); Day(Current); Day(DueDate -

Day(DueDate)))) & " Days"

)

--- END ---

[Lee Smith]

Minor typo in the 8th Line, GetAsNumber(Mod(Month(Due Date), [color:blue]Due Date s/b one word [color:blue]DueDate.

Lee

[Rich S]

Thank you very much, John, for the formula and for the typo watch, Lee.

Oops, I found a problem: in record 1--where the date is 3.22.06--your calculation shows 1 year 6 months and 22 days; it should be 0 years. I'll dink around with your formula and will hopefully see what needs to be tweaked. After that, it's tweaking the formulas a little more so a 0 output value doesn't print as zero, but a blank.

John, if that's a recent picture of you on the sidebar then I'm REALLY impressed!

Edited August 31, 2006 by Guest
Found a problem

[John Mark Osborne]

Thanks for catching the typo, Lee!

[John Mark Osborne]

Actually, the formula shows negative 1 year (-1). I would recommend adding a Case statement around the entire formula to display "Past Due" or something like that when today's date is greater than the due date. This will prevent negative numbers.