What's wrong with my calculation??

[Kittrick]

Here's an interesting problem. I have a calendar I made from scratch in Filemaker 5.5 . The calendar has 37 spaces named day1 to day37 (in case the 1st on a month with 31 days lands on a Saturday). All of the days adjust fine in the calendar except March 1st 2004 (a leap year) and 2009 which are supposed to appear in the box after the 28th. March 1st 2004 lands on a Monday (because of the leap year) and March 1st 2009 lands on a Sunday. The formula is for the day of March 1st.

I also in the formula have the variable day1a and dayofweek1.

day1a is used to convert the date in the form mm/dd/yyyy (variable day1) to a single day by the formula day1=1 and put the date on the calendar accordingly.

dayofweek1 just names the days of the week by the formula DayName(day1).

To illustrate what my calendar looks like, I present the following diagram:

------------------------------------------------

Su M T W Th F Sat

|day1 day2 day3 day4 day5 day6 day7 |

|day8 day9 day10 day11 day12 day13 day14 |

|day15 day16 day17 day18 day19 day20 day21 |

|day22 day23 day24 day25 day26 day27 day28 |

|day29 day30 day31 day32 day33 day34 day35 |

|day36 day37 |

-------------------------------------------------

Here is the formula itself for day29:

-------------------------------------

If(dayofweek1="Sunday" and month="February" , day1+28, If(dayofweek1="Sunday" and month ="January" or "February" and year="2004" or "March" or "April" or "May" or "June" or "July" or "August" or "September" or "October" or "November" or "December", day1a +28,If(dayofweek1="Monday",day1a+27,If(dayofweek1="Tuesday",day1a+26,If(dayofweek1="Wednesday",day1a+25,If(dayofweek1="Thursday",day1a+24,If(dayofweek1="Friday",day1a+23,If(dayofwe ek1="Saturday",day1a+22,""))))))))

------------------

Like I was saying, it works every time EXCEPT when I list the months of February 2004 and 2009. Any suggestions???

[Vaughan]

I just worked out quite a simpler method.

First you need a global field with the monthname and another with the year (all 4 digits!).

Now make a calculation field FirstDayofMonth that works out the dayOfWeek of the first of the month:

code:

---

DayName(Date(Case(month = "January", 1,

month = "February", 2,

month = "March", 3,

month = "April", 4,

month = "May", 5,

month = "June", 6,

month = "July", 7,

month = "August", 8,

month = "September", 9,

month = "October", 10,

month = "November", 11,

month = "December", 12),

1, year))

We also need to make a calculation field LengthOfMonth that works out how long each month is:

code:

---

Case(month = "January", 31,

month = "February" and Mod(year, 4) = 0, 29,

month = "February" and Mod(year, 100) = 0, 29,

month = "February" and Mod(year, 4) * 0, 28,

month = "March", 31,

month = "April", 30,

month = "May", 31,

month = "June", 30,

month = "July", 31,

month = "August", 31,

month = "September", 30,

month = "October", 31,

month = "November", 30,

month = "December", 31)

This assumes English month names.

The formula for day27 (out of the 37 fields) would look like this:

code:

---

If(27 - FirstDayofMonth .gte. 0 and

(27 - FirstDayofMonth + 1) .lte. LengthOfMonth,

27 - FirstDayofMonth + 1,

"")

To get the other days, substitute the day number for the three "27" values.

Note that .gte. is "greater than or equals" and .lte. is "less than or equals".

The algorithm is this:

"27 - FirstDayofMonth .gte. 0" hides the number if the day is before the first of the month

"(27 - FirstDayofMonth + 1) .lte. LengthOfMonth" hides the number if it is greater than the number of days in the month

"27 - FirstDayofMonth + 1" is the number that is displayed in the calendar field.

---

Now having said all this, I'm using FMP 5.5v2 and some of the calculations fields were wrong -- as in didn't calculate properly. No typos, no bad logic (I think).

I have to go home now so no time to test it further, but I'm worried about FMP 5.5v2 having problems -- bugs.

[ February 06, 2002, 10:09 PM: Message edited by: Vaughan ]

[Kittrick]

Thanks for the reply! Your idea is certainly easier than mine. I was worried too about weird bugs in fields when things are calculated as well.

[BobWeaver]

Vaughan:

Just had a quick look at your days-in-month formula and it doesn't look like it handles leap years correctly.

Leap year rule:

1. Not divisible by 4, then not a leap year.

2. Divisible by 4, then it is a leap year, unless...

3. Divisible by 100, in which case it's not a leap year, unless...

4. Divisible by 400, in which case it is a leap year after all.

Hence 1900 was not a leap year, but 2000 was.

So the formula should be:

code:

---

Case(month = "January", 31,

month = "February" and Mod(year, 400) = 0, 29,

month = "February" and Mod(year, 100) = 0, 28,

month = "February" and Mod(year, 4) = 0, 29,

month = "February", 28,

month = "March", 31,

month = "April", 30,

month = "May", 31,

month = "June", 30,

month = "July", 31,

month = "August", 31,

month = "September", 30,

month = "October", 31,

month = "November", 30,

month = "December", 31)

[Thom]

Why not let FM worry about leap years, etc.? Shorten the month number calc to this:

code:

---

monthNumber(calc,number) =

(

Position

(

"JanFebMarAprMayJunJulAugSepOctNovDec",

Left(monthName,3),1,1

) + 2

) / 3

Create a calc to find the number of the first day of the week of the month (Sunday = 1, Saturday = 7)

code:

---

firstDayOfWeek(calc,number) =

DayofWeek(Date(monthNumber, 1, year))

If you'd like to show a few days from the months before and after the current one, use this pattern for calendar days 1-6 and 29-37 (you can use it for all 37 (or 42) days, really)

code:

---

sun1(calc,number) =

Day(Date(monthNumber, 2 - firstDayOfWeek, year))

mon1(calc,number) =

Day(Date(monthNumber, 3 - firstDayOfWeek, year))

tue1(calc,number) =

Day(Date(monthNumber, 4 - firstDayOfWeek, year))

...

If you'd prefer to show numbers for the current month only, use this for days 1-6 and 29-37:

code:

---

sun1(calc,number) =

If(Month(Date(monthNumber,2-firstDayOfWeek,year)) = monthNumber,

2 - firstDayOfWeek,

TextToNum( "" )

)

mon1(calc,number) =

If(Month(Date(monthNumber,3-firstDayOfWeek,year)) = monthNumber,

3 - firstDayOfWeek,

TextToNum( "" )

)

tue1(calc,number) =

If(Month(Date(monthNumber,4-firstDayOfWeek,year)) = monthNumber,

4 - firstDayOfWeek,

TextToNum( "" )

)

For calendar days 7-28, use this pattern:

code:

---

sat1(calc,number) = 8 - firstDayOfWeek

sun2(calc,number) = 9 - firstDayOfWeek

mon2(calc,number) = 10 - firstDayOfWeek

The number in the field name represents the week, or row, of the calendar.

You don't need it for this calendar, but here's an easy way to find the number of days in a month:

code:

---

monthLength(calc,number) =

Day(Date(monthNumber + 1, 0, year))

[Vaughan]

We cannot let FMP worry about the leap year because it's not an FMP function we're using, it's a function we're developing ourselves.

And Bob Weaver, full marks to you for spotting my mistake! I got mixed up with my leap years. It has been a while since the Y2K bug hasn't it!

[Kittrick]

Your code is really great! I'm having only one slight glitch when I plugged it in though. It seems to be one day off some months, and erratic results in others. I'm using FM5.5v1.

I'm not sure if it's a problem on my side or something that can be resolved in the code. I was just testing out Sun1 and Mon1 versus a calendar I have here in the office for clarity.

The erratic results were when I compared the Sun1 and Mon1 for the month of April 2002 were:

Sun1:26

Mon1:27

when they should have been

Sun1:31 (of March)

Mon1:1 (of April)

Any thoughts or fixes??

[rivet]

Stop... Month lengths, Leap years are already built in to filemaker you do not have rebuild it.

The calc is:

((Desired Month) + 1 Month) - 1day

Good Luck

[Thom]

I'm using FM5.0v3, and all months for 2002 calculate correctly. I can send you a demo, if you'd like.

[Kittrick]

quote:

---

Originally posted by Thom:

I'm using FM5.0v3, and all months for 2002 calculate correctly. I can send you a demo, if you'd like.

---

I'm not saying I didn't believe you, I was just wondering why I got erratic results. It could be FM 5.5v1 or even my computer...who knows. I'll try it out on another (newer) computer to be sure.The logic is sound in your calcs.