Your start of the week formula could be simplified to =
Datefield - DayOfWeek ( Datefield ) + 1
7 * Div ( Datefield ; 7 )
There is no need to enter the day of week manually.
To calculate the start of the fortnight, you can use =
14 * Div ( Datefield ; 14 )
Michael, take so much for your reply, you're fantastic! Sorry for the tardy response, I thought I'd already sent this reply not long after you replied to me (which was ages ago) but it mustn't have been done correctly so my apologies.
This all seems to go along brilliantly. One question I did have was regarding the "Div" function, so I thought I'd post it here. I'd never used it before and it works excellently. So, if Sunday = 1 and Monday = 2 etc in the DayOfWeek function, I'm assuming fmp uses this same logic throughout their whole calculation set. If this is the case, then I'm guessing the very first day fmp uses as its Day 1 of all time, way way way back to the beginning of time ;) would be counted as Day 1 and is a Sunday.
If this is the case, then why does dividing, say, Day 28 of fmp historical time (which would be a Saturday using this logic), by 14 (which =2), then finding the closest integer at or below this (which is 2) then multiplying by 14 (=28), come up with the answer as a Sunday? My rough calcs make this number a Saturday, not a Sunday 13 days prior. Or does fmp have the first day of history as Day 0? And then the next Sunday as Day 14 etc?
Thanks once again for the wonderful advice, it has been a great help!
1 of 1 people found this helpful
The very first day in Filemaker's calendar is Monday, January 1, 0001. The 28th day of the calendar - January 28, 0001 - is a Sunday.
Note that dividing 1 by 14 leaves a remainder, but dividing 28 by 14 does not. Thus the result of =
Div ( Datefield ; 14 )
is always a Sunday.
Great, thanks for that! That makes sense that Day 1 of the fmp calendar and Day 1 of a week are different. Will use this more often now I understand it more. Thanks again, Mark.