|
In one of my Filemaker project I needed to make some date calculations to find out different dates for the single reporting need of the company. Below I present some of the calculations used for the purpose. All the calculations for date fields below are based on the current date. 1. Current Date gToday=Get(Current Date) Based on this dates the other calculations for the dates will be done. 2. To get the first Day of the week To calculate the first day of the week we need a custom function. The Custom function makes use three parameters and they are week (i.e the WeekOf Year ()), then the year of the week and finally it takes into account the type .Type is considered depending on whether Sunday is considered as the first day of the month or Monday is considered as the first day of the month. FirstDayOfWeeks (week;Yearofweek;type)———Custom function |
//Calculate first date of a given week in a given year // type 0 takes sunday as the first day of the week, type 1 takes monday Let ( [ //the first of january of the given year xFirstYearDate = Date ( 1 ; 1 ; yearOfWeek ) ; //the weekday number of the first of january of the given year xFirstWeekDay = If ( type ; Choose ( FirstDayOfWeek ( xFirstYearDate ) - 1 ; 7 ; 1 ; 2 ; 3 ; 4 ; 5 ; 6 ) ; FirstDayOfWeek( xFirstYearDate ) ) ]; //multiply the week number, make corrections and add it to the first day of the given year xFirstYearDate + ( ( week - If ( xFirstWeekDay > 4 ; 0 ; 1 ) ) * 7 ) - xFirstWeekDay + 1 ) cWTD =FirstDayOfWeeks ( WeekOfYear (gToday ) ; Year ( gToday ) ;1)
3. To calculate the start of week prior to the week of the current date.
cPWTDstart=cWTD – 7
4. To calculate the start of week prior to the week of the current date.
cPWTDend=gToday – 7
5. To calculate the first date of the month.
cMTD=Date (Month(gToday) ; “01” ; Year(gToday) )
6. To get the start of the prior month of the current date.
xpmtdstart=If(Month(gToday)=1,Date(“12”;”01”;Year(gToday)-1),Date(Month(gToday)-1;
”01”;Year(gToday))
cPMTDstart=xpmtdstart
7. To get the end of the prior month of the current date.
cPMTDend=Date( Month(cPMTDstart) ; Day ( gToday ) ; Year(cPMTDstart) )
8. To get the start of prior month of the current month.
cPMstart= cPMTDstart
9. To get the end of prior month of the current month.
cPMend= DATE(YEAR(cPMStart),MONTH(cPMStart),IF(MONTH(cPMStart)=2,
IF(AND(MOD(YEAR(cPMStart),4)=0,MOD(YEAR(cPMStart),100)0),29,28),IF(OR(MONTH(cPMStart)=4,MONTH(cPMStart)=6,
MONTH(cPMStart)=9,MONTH(cPMStart)=11),30,31)))
10. Calculate the quarter of the current date.
xmod=Case ( Mod ( Month (gToday ) ;3)=0;3; Mod ( Month (gToday ) ;3)—Calculated field.
cQTD = Date (Month (gToday ) – xmod + 1; 1 ; Year ( gToday )
11. To get the start of the prior quarter of the current date.
cPQTDstart=Date (Month ( gToday ) – 3 ; “01” ; Year(gToday) )
12. To get the end of the prior quarter of the current date.
cPQTDend=Date (Month ( gToday ) – 3 ; Day (gToday ) ; Year(gToday) )
13. To get prior quarter of the current quarter of the current date.
cPQ= cPQTDstart
14. To get the start of the prior quarter to date.
cPQstart=Date ( Month(gToday) ; “01” ; Year(gToday) – 1 )
15. To get the end of the prior quarter to date.
cPQend=Date (Month(gToday) ; Day (gToday ) ;Year(gToday) – 1 )
16. To get the start of the prior year quarter to date
cPYQTDstart=Date(Year(cPQTDstart)-1,Month(cPQTDstart),Day(cPQTDstart))
17. To get the end of the prior year quarter to date
cPYQTDend=Date(Year(cPQTDend)-1,Month(cPQTDend),Day(cPQTDend))
18. To get the year of the current date
cYTD=Date ( “01” ; “01” ; Year(gToday) )
19. To get the start of the prior year of the current date
cPYTDstart=Date (Month(cYTDstart);Day(cYTDstart) ; Year(cYTDstart) – 1 )
20. To get the end of the prior year of the current date
cPYTDend=Date ( Month(cYTDend) ; Day (cYTDend ) ; Year(cYTDend) – 1 )