Work around to fix calander start date

[MartinaL]

Hi,

I am making some changes to a system that was originally created by someone else and I need to fix the following problem.

He had previously set up a calander structure in FMP6 and wanted the weeks to start on monday so he set a global offset field to take 1 off the day of the week when setting up the screen.

The issue is when months (like October 2006) start on a sunday so when you take 1 off DayOfWeek you get 0 instead of going to 7.

Here is the line of code that I think he is using to set this, but I can't seem to work out how to fix it??

DayofWeek( gdTempDate ) - gnWeekStartOffset

I tried to achieve this by changing this calculation to the following but it didn't change anything;

If(NumToText(DayofWeek( gdTempDate ))="1", 7, DayofWeek( gdTempDate ) - gnWeekStartOffset)

Any help would be greatly appreciated!

[BobWeaver]

I'm taking a wild guess here, because there isn't really enough information to tell exactly what is happening, but you could try this:

If(DayofWeek( gdTempDate ) - gnWeekStartOffset=0, 7, DayofWeek( gdTempDate ) - gnWeekStartOffset)

[LaRetta]

Or Mod ( gdTempDate - gnWeekStartOffset [color:blue]; 7 ) + 1

... maybe.

Ooops - vs. 6. Change that semicolon to a comma. :^)

Edited September 19, 2006 by Guest

[MartinaL]

Tried both and unfortunetly neither of them work.

Maybe I am missing something.

Here is the code for the script, it is so confusing to me being new to FMP but unfortunetly the original programmer is no longer available to fix it :

Ticket2.zip

[BobWeaver]

If you just want to change the result to 7 when it returns a zero, then the formula I gave should do it. So, it looks like you need to change something else. I had a look at the script you posted, but without knowing what your fields and layouts look like, it's very hard to trace through it and figure it out.

[MartinaL]

I am obviously way out of my depth here! :

Basically I assumed this was the line of code that was changing the week start day to Monday instead of Sunday, so I thought that the reason that with months that start on Sunday it doesn't work was because 1 - 1 (which is what I assume the global value of gnWeekOffset was) is 0 so it isn't working, so I thought it need to be set to 7.

Am I way off? And am i reading what this script does wrong?

[comment]

I have no idea what your script does (or why), but if you enter any date into a global gDate field, the first Monday of your calendar (i.e. the Monday of the week that contains the first day of the month of gDate, with weeks starting on Monday) can be computed this way:

Let ( [

first = gDate - Day ( gDate ) + 1

] ;

first - DayOfWeek ( first - 1 ) + 1

)

[MartinaL]

The problem is not with months starting on Monday but months starting on Sunday

[comment]

Yes, I understand that. That's why the above calculation returns Sep 25, 2006 (Monday) for any date in October 2006.

[LaRetta]

Let() isn't in vs. 6? Please clarify whether you are still on vs. 6.

[comment]

The same calc in pre-7 version:

gDate - Day ( gDate ) - DayOfWeek ( gDate - Day ( gDate ) ) + 2

[MartinaL]

Yes still version 6.

Edited September 22, 2006 by Guest

[MartinaL]

So what will this calculation above do?

[comment]

As I explained, it will return the date of the first Monday you need to display in a month view.

[MartinaL]

I am really confused.

I understand that the above equation will return the first monday of the month, but how do I use that in my original calculation to make all of the months start on monday instead of sunday by offestting the first day of the week start day by 1?

Sorry for not getting this but I am relitively new to FMP.

I am using version 6.

[comment]

I understand that the above equation will return the first monday of the month

No it doesn't. It returns the Monday of the week in which the month starts.

how do I use that in my original calculation

I cannot answer that - because I don't know how your solution works. I assumed it starts by computing the first day of the first week that needs to be displayed for that month, and uses that as the anchor for calculating the rest of the monthly grid. At least that's how I would do it - so changing the anchor from Sunday to Monday would be all that it takes.

[MartinaL]

The comments above the calculation that I assume does this in the original script says "Work out the number of the cell that the first day of the month will go in".

So by using the calculation that you have given me it return the date of the first monday in the month, so if the month starts on sunday (ie October 2006) it will return 02/10/2006 (as this is the first monday in October). Am I on the right track?

So in order to use this to fill my grid for the calendar gnStartDay should be what? I still don't see how this will help me set the number of the cell for the first day of the month?

The original calculation to determine the first day on the month as a cell number was;

DayOfWeek(gdTempDate) - gnWeekStartOffset

This works perfectly for every month except those that start on Sundays.

[comment]

if the month starts on sunday (ie October 2006) it will return 02/10/2006 (as this is the first monday in October). Am I on the right track?

No. It will return 25/9/2006. As you can (hopefully) see, that is the anchor date for displaying the month of October 2006, with the week starting on Monday.

I cannot answer the rest of your questions without knowing what gdTempDate and gnWeekStartOffset mean.

Perhaps changing the offset one day forward or six backwards might do it.

[MartinaL]

gdTempDate is the first date entered in a date range which is used to loop through and populate the calender with events (ie if 01/01/2006...31/12/2006 is entered then gdTempDate is 01/01/2006).

gnWeekStartOffset I assume is 1 (as I didn't originally write this program) and is currently being used to offset the starting day of the week from Sunday to Monday.

So because the system is set with sunday as the start of the week the DayOfTheWeek is as follows;

Sunday - 1

Monday - 2

Tuesday - 3

...

Saturday - 7

Etc

But we wanted Monday as the first day so the offest is supposed to change the DayOfTheWeek to the following;

Monday - 1

Tuesday - 2

...

Sunday - 7

But if you do this on months that start on sunday you end up with sunday as 0 instead of 7.

[MartinaL]

Okay so the anchor date is the 25th Sep.

The whole point of this is to get the cell that the first day of the month needs to start in.

I have attached a pic of this for the current date setup with the first day of the month as sunday, and with the first day as Monday.

Basically i don't need a date for the first monday on the calender grid I need to know the cell number, ie Oct 2006 should be 7.

[BobWeaver]

It seems we are back to where we started. If the day number calculates out to zero, then change it to seven which is what I had in my first post. See attached.

StartDay.fp7.zip

[MartinaL]

I know but for some reason this wasn't working.

I will try it again

[BobWeaver]

In reviewing what I posted earlier, I probably misinterpreted what your input fields represented. But, this example file doesn't leave much to chance.

[comment]

I'd suggest you attach your file.

[MartinaL]

Unfortunetly i can't open this because I am using version 6.

[MartinaL]

I can't attach the file unfortunetly because it is way to large and it is connected to about 10 other databases. That's the problem with version 6 you couldn't have more than one table in a database!

[BobWeaver]

Unfortunetly i can't open this because I am using version 6.

Oops. See new attachment here:

StartDay.fp5.zip