Week of Month

[Ocean West]

I found a way to get the week of the month from a date field. I first thought it would be easy but found out that FMP doesn't have a week of month function...

Enjoy...

code:

---

Case(

(Day(Date) - DayofWeek(Date)) < 1, 1,

(Day(Date) - DayofWeek(Date)) < 8, 2,

(Day(Date) - DayofWeek(Date)) < 15, 3,

(Day(Date) - DayofWeek(Date)) < 22, 4,

(Day(Date) - DayofWeek(Date)) < 31, 5,"")

---

[Keith Silva]

Thanks very much for this. I'm going to be using it very soon.

Regards, Keith Silva

[pmmarketing]

Thanks a lot!!! That really helped me out of a bind. I plan to post my calendar program that I modified from the samples database to all who want to use it. I am still working out a few bugs but the final product will have event repetition by week/month, printable calendar layouts, multi-user capable, and MYSQL web integration.

Regards,

Adrian

[Lee Smith]

Hi Steve,

Thanks for sharing. However, look out for months that have 6 weeks in them like 3312003

[Ugo DI LUCA]

Good call Lee....

Let see...

If the First Sunday of the Month for this date falls in the same week than the first day of this given month, then the "WeekofMonth" could be easily calculated using the gap between the WeekofYear(First Sunday of Month) and the WeekofYear for the given date.

If not, we should add 1 week to this calc.

So maybe this would work

WeekofMonth =

(WeekofYear(date)+1)-(WeekofYear(DATE(1,1,Year(date)) - Mod(DayofWeek(DATE(1,1,Year(date))) +6, 7) + (WeekofYear(Date(Month(date),1,Year(date))) * 7))- not WeekofYear(Date(1,1,Year(date)) - Mod(DayofWeek(Date(1,1,Year(date))) +6, 7) + (WeekofYear(Date(Month(date),1,Year(date))) * 7)) = WeekofYear(Date(Month(date),1,Year(date))))

where

[Ugo DI LUCA]

Oh well....

First the above calc wasn't returning a correct result if the first day of year was a Sunday.

Second, I just love these easy calculations you won't even try at first, sure it won't produce accurate rsults.

In fact,

(WeekofYear(date)+1)-WeekofYear(Date(Month(date),1,Year(date))) is working

[Lee Smith]

Hi Ugo,

Nice job! I couldn't break it.

Now watch someone come along with a choose or mod calc that makes it shorter

Lee

[Ugo DI LUCA]

Lee,

I doubt a Case or Mod could make it smaller than it is right now...

(WeekofYear(date)+1)-WeekofYear(Date(Month(date),1,Year(date)))

Maybe something like

Choose(WeekofYear(date)-WeekofYear(Date(Month(date),1,Year(date))),1,2,3,4,5,6)

And BTW, here's the corrected useless complicated calc

(WeekofYear(date)+1)-(WeekofYear(Date(1,1,Year(date)) - Mod(DayOfWeek(Date(1,1,Year(date))) +6, 7) + (WeekofYear(Date(Month(date),1,Year(date))) * 7))-

not (Year(Date(1,1,Year(date)) - Mod(DayofWeek(Date(1,1,Year(date))) +6,7)) =Year(date) and not Day(Date(1,1,Year(date)) - Mod(DayofWeek(Date(1,1,Year(date))) +6, 7))=1)))

[Lee Smith]

Hi Ugo,

  Quote

Ugo DI LUCA said:

I doubt a Case or Mod could make it smaller than it is right now...

That was suppose to be a joke

Lee

[-Queue-]

If it's any consolation, I giggled.

[SteveB]

Since we're on weeks calcs, does anybody have 2 calcs that will return the Sunday date and the Saturday date of a week (assuming the week starts on Sunday)? Click on Tues, and display his schedule for Sunday thru Sat of that week.

Steve

[-Queue-]

Sunday = datefield - DayofWeek( datefield ) + 1

Saturday = datefield - DayofWeek( datefield ) + 7

[SteveB]

That was easy. Thanks, Steve