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?