4 Replies Latest reply on Jun 3, 2012 9:38 PM by mkaizik

    Calculating date a fortnight begins


      I'm sure there's a simple solution to my problerm, but I'm trying to work out how to create a calculation field that calculates the date a fortnight begins.


      I currently have records that display a date that is manually entered as a particular day of the week in a "Day of the week" field. From this, I calculate the "Week beginning" date using the following formula:


      Case ( Day of the week = "Sunday" ; Date ; Day of the week = "Monday" ; Date - 1 ; Day of the week = "Tuesday" ; Date - 2 ; Day of the week = "Wednesday" ; Date - 3 ; Day of the week = "Thursday" ; Date - 4 ; Day of the week = "Friday" ; Date - 5 ; Day of the week = "Saturday" ; Date - 6 ; Date )


      (NB: the first day of the week for us is Sunday)



      This "Week beginning" date is helpful as I can then summarise my data by producing a report that groups my data into the "Week beginning" date, summarising all records from that week. I'd like to be able to do the same for 2 weeks of data, as the pays run in fortnights. If the next fortnight pay starts on Sunday, April 1, 2012, how do I set up a "Fortnight beginning date" calculation field using a simple manually entered date field from a record as above, and how do I have it so that it lines up with April 1, 2012, and then calculates it correctly for each date in the future and its associated "Fortnight beginning date", as well as for all previous dates and their respective fortnights?

        • 1. Re: Calculating date a fortnight begins

          Your start of the week formula could be simplified to =


          Datefield - DayOfWeek ( Datefield ) + 1


          or =


          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 )
          • 2. Re: Calculating date a fortnight begins

            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!



            • 3. Re: Calculating date a fortnight begins

              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.

              1 of 1 people found this helpful
              • 4. Re: Calculating date a fortnight begins

                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.