forked from petrowsky/fmpfunctions
-
Notifications
You must be signed in to change notification settings - Fork 1
/
date.ranges.fmfn
110 lines (91 loc) · 4.6 KB
/
date.ranges.fmfn
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
/*
=====================================================
date.ranges( string; separator; withtime )
RETURNS: (string) FileMaker specific date range string.
DEPS: none
NOTES: The withtime value is a bool which will account for adding 24
hours when used with a timestamp field type. Normal FMP dates
assume 12:00 AM with just a raw date
=====================================================
*/
Let(
[
_Now = Get( CurrentDate );
_TimeValue = If ( withtime; 1; 0) // Option for adding a day to the end date because fmp assumes 12:00 AM with raw date.
];
Case(
string = "Today";
_Now & separator & _Now + _TimeValue;
string = "Yesterday";
Let( _Yesterday = _Now - 1; _Yesterday & separator & _Yesterday + _TimeValue);
string = "Tomorrow";
Let( _Tomorrow = _Now + 1; _Tomorrow & separator & _Tomorrow + _TimeValue);
string = "ThisWeek";
_Now - DayOfWeek(_Now) + 1 & separator & _Now + ( 7 - DayOfWeek(_Now)) + _TimeValue;
string = "LastWeek";
_Now - DayOfWeek(_Now) - 6 & separator & _Now - DayOfWeek(_Now) + _TimeValue;
string = "NextWeek";
Let( sunday = _Now + ( 8 - DayOfWeek(_Now)); sunday & separator & sunday + 6 + _TimeValue);
string = "ThisMonth";
Let( firstday = Date( Month(_Now); 1; Year(_Now)); firstday & separator & Date( Month(firstday) + 1; 0; Year(firstday)) + _TimeValue);
string = "LastMonth";
Let( lastday = Date( Month(_Now); 0; Year(_Now)); Date( Month(lastday); 1; Year(lastday)) & separator & lastday + _TimeValue);
string = "NextMonth";
Let( firstday = Date( Month(_Now) + 1; 0; Year(_Now)) + 1; firstday & separator & Date( Month(firstday) + 1; 0; Year(firstday)) + _TimeValue);
string = "ThisYTD";
Date(1; 1; Year(_Now)) & separator & _Now + _TimeValue;
string = "LastYTD";
Date(1; 1; Year(_Now)-1) & separator & Date(Month(_Now); Day(_Now); Year(_Now) - 1) + _TimeValue;
string = "NextYTD";
Date(1; 1; Year(_Now) + 1) & separator & Date(Month(_Now); Day(_Now); Year(_Now) + 1) + _TimeValue;
string = "ThisYear";
Date(1; 1; Year(_Now)) & separator & Date(12; 31; Year(_Now)) + _TimeValue;
string = "LastYear";
Date(1; 1; Year(_Now) - 1) & separator & Date(12; 31; Year(_Now) - 1) + _TimeValue;
string = "NextYear";
Date(1; 1; Year(_Now) + 1) & separator & Date(12; 31; Year(_Now) + 1) + _TimeValue;
string = "ThisQuarter" or
string = "LastQuarter" or
string = "NextQuarter";
Let (
xMod = Case (
Mod( Month( _Now ) ; 3 ) = 0; 3;
Mod( Month( _Now ) ; 3 ) );
Case(
string = "ThisQuarter";
Date( Month(_Now) - xMod + 1; 1; Year(_Now) ) & separator & _Now + _TimeValue;
string = "LastQuarter";
Date( Month(_Now) - xMod - 2; 1; Year(_Now) ) & separator & Date( Month(_Now) - xMod + 1; 1; Year(_Now) ) - 1 + _TimeValue;
string = "NextQuarter";
Date( Month(_Now) - xMod + 4; 1; Year(_Now) ) & separator & Date( Month(_Now) - xMod + 7; 1; Year(_Now) ) - 1 + _TimeValue
)
);
string = "test";
"Today = " & date.ranges("Today"; separator; withtime) & ¶ &
"Yesterday = " & date.ranges("Yesterday"; separator; withtime) & ¶ &
"Tomorrow = " & date.ranges("Tomorrow"; separator; withtime) & ¶ &
"ThisWeek = " & date.ranges("ThisWeek"; separator; withtime) & ¶ &
"LastWeek = " & date.ranges("LastWeek"; separator; withtime) & ¶ &
"NextWeek = " & date.ranges("NextWeek"; separator; withtime) & ¶ &
"ThisMonth = " & date.ranges("ThisMonth"; separator; withtime) & ¶ &
"LastMonth = " & date.ranges("LastMonth"; separator; withtime) & ¶ &
"NextMonth = " & date.ranges("NextMonth"; separator; withtime) & ¶ &
"ThisYTD = " & date.ranges("ThisYTD"; separator; withtime) & ¶ &
"LastYTD = " & date.ranges("LastYTD"; separator; withtime) & ¶ &
"NextYTD = " & date.ranges("NextYTD"; separator; withtime) & ¶ &
"ThisYear = " & date.ranges("ThisYear"; separator; withtime) & ¶ &
"LastYear = " & date.ranges("LastYear"; separator; withtime) & ¶ &
"NextYear = " & date.ranges("NextYear"; separator; withtime) & ¶ &
"ThisQuarter = " & date.ranges("ThisQuarter"; separator; withtime) & ¶ &
"LastQuarter = " & date.ranges("LastQuarter"; separator; withtime) & ¶ &
"NextQuarter = " & date.ranges("NextQuarter"; separator; withtime)
)
)
/*
Original by
Andy Frazier, Mx4Px
http://www.andyfrazier.com
via Briandunning.com
Compliments to Excelisys
Modified by Matt Petrowsky
*/