Shutdown: help the style leak printer out a bit.
[gnumeric.git] / plugins / fn-date / functions.c
blobcdb1c48a276892cf2a748d6566eff4455057372a
1 /* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
2 /*
3 * fn-date.c: Built in date functions.
5 * Authors:
6 * Miguel de Icaza (miguel@gnu.org)
7 * Jukka-Pekka Iivonen (iivonen@iki.fi)
8 * Morten Welinder <terra@gnome.org>
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License
21 * along with this program; if not, see <https://www.gnu.org/licenses/>.
23 #include <gnumeric-config.h>
24 #include <gnumeric.h>
25 #include <func.h>
27 #include <parse-util.h>
28 #include <cell.h>
29 #include <gnm-datetime.h>
30 #include <value.h>
31 #include <mathfunc.h>
32 #include <gnm-format.h>
33 #include <workbook.h>
34 #include <sheet.h>
35 #include <collect.h>
36 #include <gnm-i18n.h>
38 #include <math.h>
39 #include <string.h>
40 #include <stdlib.h>
42 #include <goffice/goffice.h>
43 #include <gnm-plugin.h>
45 GNM_PLUGIN_MODULE_HEADER;
47 #define DAY_SECONDS (3600*24)
48 #define DATE_CONV(ep) sheet_date_conv ((ep)->sheet)
50 static GnmValue *
51 make_date (GnmValue *res)
53 value_set_fmt (res, go_format_default_date ());
54 return res;
57 static int
58 value_get_basis (const GnmValue *v, int defalt)
60 if (v) {
61 gnm_float b = value_get_as_float (v);
63 if (b < 0 || b >= 6)
64 return -1;
65 return (int)b;
66 } else
67 return defalt;
70 /***************************************************************************/
72 static GnmFuncHelp const help_date[] = {
73 { GNM_FUNC_HELP_NAME, F_("DATE:create a date serial value")},
74 { GNM_FUNC_HELP_ARG, F_("year:year of date")},
75 { GNM_FUNC_HELP_ARG, F_("month:month of year")},
76 { GNM_FUNC_HELP_ARG, F_("day:day of month")},
77 { GNM_FUNC_HELP_DESCRIPTION, F_("The DATE function creates date serial values. 1-Jan-1900 is serial value 1, 2-Jan-1900 is serial value 2, and so on. For compatibility reasons, a serial value is reserved for the non-existing date 29-Feb-1900.") },
78 { GNM_FUNC_HELP_NOTE, F_("If @{month} or @{day} is less than 1 or too big, then the year and/or month will be adjusted.") },
79 { GNM_FUNC_HELP_NOTE, F_("For spreadsheets created with the Mac version of Excel, serial 1 is 1-Jan-1904.") },
80 { GNM_FUNC_HELP_EXAMPLES, "=DATE(2008,1,1)" },
81 { GNM_FUNC_HELP_EXAMPLES, "=DATE(2008,13,1)" },
82 { GNM_FUNC_HELP_EXAMPLES, "=DATE(2008,1,-10)" },
83 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
84 { GNM_FUNC_HELP_SEEALSO, "TODAY,YEAR,MONTH,DAY"},
85 { GNM_FUNC_HELP_END }
88 static GnmValue *
89 gnumeric_date (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
91 gnm_float year = value_get_as_float (argv [0]);
92 gnm_float month = value_get_as_float (argv [1]);
93 gnm_float day = value_get_as_float (argv [2]);
94 GDate date;
95 GODateConventions const *conv = DATE_CONV (ei->pos);
97 if (year < 0 || year >= 10000)
98 goto error;
99 if (!gnm_datetime_allow_negative () && year < 1900)
100 year += 1900; /* Excel compatibility. */
101 else if (year < 1000)
102 year += 1900; /* Somewhat more sane. */
104 /* This uses floor and not trunc on purpose. */
105 month = gnm_floor (month);
106 if (gnm_abs (month) > 120000) /* Actual number not critical. */
107 goto error;
109 /* This uses floor and not trunc on purpose. */
110 day = gnm_floor (day);
111 if (day < -32768 || day >= 32768)
112 day = 32767; /* Absurd, but yes. */
114 g_date_clear (&date, 1);
116 g_date_set_dmy (&date, 1, 1, (int)year);
117 gnm_date_add_months (&date, (int)month - 1);
118 gnm_date_add_days (&date, (int)day - 1);
120 if (!g_date_valid (&date) ||
121 g_date_get_year (&date) < (gnm_datetime_allow_negative ()
122 ? 1582
123 : go_date_convention_base (conv)) ||
124 g_date_get_year (&date) >= 11900)
125 goto error;
127 return make_date (value_new_int (go_date_g_to_serial (&date, conv)));
129 error:
130 return value_new_error_NUM (ei->pos);
133 /***************************************************************************/
135 static GnmFuncHelp const help_unix2date[] = {
136 { GNM_FUNC_HELP_NAME, F_("UNIX2DATE:date value corresponding to the Unix timestamp @{t}")},
137 { GNM_FUNC_HELP_ARG, F_("t:Unix time stamp")},
138 { GNM_FUNC_HELP_DESCRIPTION, F_("The UNIX2DATE function translates Unix timestamps into the corresponding date. A Unix timestamp is the number of seconds since midnight (0:00) of January 1st, 1970 GMT.") },
139 { GNM_FUNC_HELP_EXAMPLES, "=UNIX2DATE(1000000000)" },
140 { GNM_FUNC_HELP_SEEALSO, "DATE2UNIX,DATE"},
141 { GNM_FUNC_HELP_END }
144 static GnmValue *
145 gnumeric_unix2date (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
147 gnm_float futime = value_get_as_float (argv [0]);
148 time_t utime = (time_t)futime;
149 gnm_float serial;
151 /* Check for overflow. */
152 if (gnm_abs (futime - utime) >= 1.0)
153 return value_new_error_VALUE (ei->pos);
155 serial = go_date_timet_to_serial_raw (utime, DATE_CONV (ei->pos));
156 if (serial == G_MAXINT)
157 return value_new_error_VALUE (ei->pos);
159 return make_date (value_new_float (serial +
160 (futime - utime) / DAY_SECONDS));
163 /***************************************************************************/
165 static GnmFuncHelp const help_date2unix[] = {
166 { GNM_FUNC_HELP_NAME, F_("DATE2UNIX:the Unix timestamp corresponding to a date @{d}") },
167 { GNM_FUNC_HELP_ARG, F_("d:date")},
168 { GNM_FUNC_HELP_DESCRIPTION, F_("The DATE2UNIX function translates a date into a Unix timestamp. A Unix timestamp is the number of seconds since midnight (0:00) of January 1st, 1970 GMT.") },
169 { GNM_FUNC_HELP_EXAMPLES, "=DATE2UNIX(DATE(2000,1,1))" },
170 { GNM_FUNC_HELP_SEEALSO, "UNIX2DATE,DATE"},
171 { GNM_FUNC_HELP_END }
174 static GnmValue *
175 gnumeric_date2unix (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
177 gnm_float fserial = value_get_as_float (argv [0]);
178 int serial = (int)fserial;
179 time_t utime = go_date_serial_to_timet (serial, DATE_CONV (ei->pos));
181 /* Check for overflow. */
182 if (gnm_abs (fserial - serial) >= 1.0 || utime == (time_t)-1)
183 return value_new_error_VALUE (ei->pos);
185 return value_new_int (utime +
186 gnm_fake_round (DAY_SECONDS * (fserial - serial)));
189 /***************************************************************************/
191 static GnmFuncHelp const help_datevalue[] = {
192 { GNM_FUNC_HELP_NAME, F_("DATEVALUE:the date part of a date and time serial value")},
193 { GNM_FUNC_HELP_ARG, F_("serial:date and time serial value")},
194 { GNM_FUNC_HELP_DESCRIPTION, F_("DATEVALUE returns the date serial value part of a date and time serial value.") },
195 { GNM_FUNC_HELP_EXAMPLES, "=DATEVALUE(NOW())" },
196 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
197 { GNM_FUNC_HELP_SEEALSO, "TIMEVALUE,DATE"},
198 { GNM_FUNC_HELP_END }
201 static GnmValue *
202 gnumeric_datevalue (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
204 return value_new_int (datetime_value_to_serial (argv[0], DATE_CONV (ei->pos)));
207 /***************************************************************************/
209 static GnmFuncHelp const help_datedif[] = {
210 { GNM_FUNC_HELP_NAME, F_("DATEDIF:difference between dates") },
211 { GNM_FUNC_HELP_ARG, F_("start_date:starting date serial value")},
212 { GNM_FUNC_HELP_ARG, F_("end_date:ending date serial value")},
213 { GNM_FUNC_HELP_ARG, F_("interval:counting unit")},
214 { GNM_FUNC_HELP_DESCRIPTION, F_("DATEDIF returns the distance from @{start_date} to @{end_date} according to the unit specified by @{interval}.") },
215 { GNM_FUNC_HELP_NOTE, F_("If @{interval} is \"y\", \"m\", or \"d\" then the distance is measured in complete years, months, or days respectively.") },
216 { GNM_FUNC_HELP_NOTE, F_("If @{interval} is \"ym\" or \"yd\" then the distance is measured in complete months or days, respectively, but excluding any difference in years.") },
217 { GNM_FUNC_HELP_NOTE, F_("If @{interval} is \"md\" then the distance is measured in complete days but excluding any difference in months.") },
218 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
219 { GNM_FUNC_HELP_EXAMPLES, "=DATEDIF(DATE(2003,2,3),DATE(2007,4,2),\"m\")" },
220 { GNM_FUNC_HELP_EXAMPLES, "=DATEDIF(DATE(2000,4,30),DATE(2003,8,4),\"d\")" },
221 { GNM_FUNC_HELP_EXAMPLES, "=DATEDIF(DATE(2000,4,30),DATE(2003,8,4),\"y\")" },
222 { GNM_FUNC_HELP_SEEALSO, "DAYS360"},
223 { GNM_FUNC_HELP_END }
226 static int
227 datedif_opt_ym (GDate *gdate1, GDate *gdate2)
229 g_assert (g_date_valid (gdate1));
230 g_assert (g_date_valid (gdate2));
232 return go_date_g_months_between (gdate1, gdate2) % 12;
235 static int
236 datedif_opt_yd (GDate *gdate1, GDate *gdate2, int excel_compat)
238 g_assert (g_date_valid (gdate1));
239 g_assert (g_date_valid (gdate2));
241 g_date_get_day (gdate1);
243 gnm_date_add_years (gdate1,
244 go_date_g_years_between (gdate1, gdate2));
245 /* according to glib.h, feb 29 turns to feb 28 if necessary */
247 if (excel_compat) {
248 int new_year1, new_year2;
250 /* treat all years divisible by four as leap years: */
251 /* this is clearly wrong, but it's what Excel does. */
252 /* (I use 2004 here since it is clearly a leap year.) */
253 new_year1 = 2004 + (g_date_get_year (gdate1) & 0x3);
254 new_year2 = new_year1 + (g_date_get_year (gdate2) -
255 g_date_get_year (gdate1));
256 g_date_set_year (gdate1, new_year1);
257 g_date_set_year (gdate2, new_year2);
260 static gboolean need_warning = TRUE;
261 if (need_warning) {
262 g_warning("datedif is known to differ from "
263 "Excel for some values.");
264 need_warning = FALSE;
269 return g_date_days_between (gdate1, gdate2);
272 static int
273 datedif_opt_md (GDate *gdate1, GDate *gdate2, gboolean excel_compat)
275 int day;
277 g_assert (g_date_valid (gdate1));
278 g_assert (g_date_valid (gdate2));
280 day = g_date_get_day (gdate1);
282 gnm_date_add_months (gdate1,
283 go_date_g_months_between (gdate1, gdate2));
284 /* according to glib.h, days>28 decrease if necessary */
286 if (excel_compat) {
287 int new_year1, new_year2;
289 /* treat all years divisible by four as leap years: */
290 /* this is clearly wrong, but it's what Excel does. */
291 /* (I use 2004 here since it is clearly a leap year.) */
292 new_year1 = 2004 + (g_date_get_year (gdate1) & 0x3);
293 new_year2 = new_year1 + (g_date_get_year (gdate2) -
294 g_date_get_year (gdate1));
295 g_date_set_year (gdate1, new_year1);
296 g_date_set_year (gdate2, new_year2);
298 /* add back the days if they were decreased by
299 gnm_date_add_months */
300 /* ( i feel this is inferior because it reports e.g.:
301 datedif(1/31/95,3/1/95,"d") == -2 ) */
302 gnm_date_add_days (gdate1,
303 day - g_date_get_day (gdate1));
306 return g_date_days_between (gdate1, gdate2);
309 static GnmValue *
310 gnumeric_datedif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
312 int date1, date2;
313 char const *opt;
314 GDate d1, d2;
315 GODateConventions const *conv = DATE_CONV (ei->pos);
317 date1 = gnm_floor (value_get_as_float (argv [0]));
318 date2 = gnm_floor (value_get_as_float (argv [1]));
319 opt = value_peek_string (argv[2]);
321 if (date1 > date2)
322 return value_new_error_NUM (ei->pos);
324 go_date_serial_to_g (&d1, date1, conv);
325 go_date_serial_to_g (&d2, date2, conv);
326 if (!g_date_valid (&d1) || !g_date_valid (&d2))
327 return value_new_error_VALUE (ei->pos);
329 if (!strcmp (opt, "d"))
330 return value_new_int (g_date_get_julian (&d2) -
331 g_date_get_julian (&d1));
332 else if (!strcmp (opt, "m"))
333 return value_new_int (go_date_g_months_between (&d1, &d2));
334 else if (!strcmp (opt, "y"))
335 return value_new_int (go_date_g_years_between (&d1, &d2));
336 else if (!strcmp (opt, "ym"))
337 return value_new_int (datedif_opt_ym (&d1, &d2));
338 else if (!strcmp (opt, "yd"))
339 return value_new_int (datedif_opt_yd (&d1, &d2, TRUE));
340 else if (!strcmp (opt, "md"))
341 return value_new_int (datedif_opt_md (&d1, &d2, TRUE));
342 else
343 return value_new_error_VALUE (ei->pos);
346 /***************************************************************************/
348 static GnmFuncHelp const help_edate[] = {
349 { GNM_FUNC_HELP_NAME, F_("EDATE:adjust a date by a number of months") },
350 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
351 { GNM_FUNC_HELP_ARG, F_("months:signed number of months")},
352 { GNM_FUNC_HELP_DESCRIPTION, F_("EDATE returns @{date} moved forward or backward the number of months specified by @{months}.") },
353 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
354 { GNM_FUNC_HELP_EXAMPLES, "=EDATE(DATE(2001,12,30),2)" },
355 { GNM_FUNC_HELP_SEEALSO, "DATE"},
356 { GNM_FUNC_HELP_END }
359 static GnmValue *
360 gnumeric_edate (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
362 GODateConventions const *conv = DATE_CONV (ei->pos);
363 gnm_float serial = value_get_as_float (argv[0]);
364 gnm_float months = value_get_as_float (argv[1]);
365 GDate date;
367 if (serial < 0 || serial > INT_MAX)
368 return value_new_error_NUM (ei->pos);
369 if (months > INT_MAX / 2 || -months > INT_MAX / 2)
370 return value_new_error_NUM (ei->pos);
372 go_date_serial_to_g (&date, (int)serial, conv);
373 gnm_date_add_months (&date, (int)months);
375 if (!g_date_valid (&date) ||
376 g_date_get_year (&date) < 1900 ||
377 g_date_get_year (&date) > 9999)
378 return value_new_error_NUM (ei->pos);
380 return make_date (value_new_int (go_date_g_to_serial (&date, conv)));
383 /***************************************************************************/
385 static GnmFuncHelp const help_today[] = {
386 { GNM_FUNC_HELP_NAME, F_("TODAY:the date serial value of today") },
387 { GNM_FUNC_HELP_DESCRIPTION, F_("The TODAY function returns the date serial value of the day it is computed. Recomputing on a later date will produce a different value.") },
388 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
389 { GNM_FUNC_HELP_EXAMPLES, "=TODAY()" },
390 { GNM_FUNC_HELP_SEEALSO, "DATE"},
391 { GNM_FUNC_HELP_END }
394 static GnmValue *
395 gnumeric_today (GnmFuncEvalInfo *ei, G_GNUC_UNUSED GnmValue const * const *argv)
397 return make_date (value_new_int (go_date_timet_to_serial (time (NULL), DATE_CONV (ei->pos))));
400 /***************************************************************************/
402 static GnmFuncHelp const help_now[] = {
403 { GNM_FUNC_HELP_NAME, F_("NOW:the date and time serial value of the current time") },
404 { GNM_FUNC_HELP_DESCRIPTION, F_("The NOW function returns the date and time serial value of the moment it is computed. Recomputing later will produce a different value.") },
405 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
406 { GNM_FUNC_HELP_EXAMPLES, "=NOW()" },
407 { GNM_FUNC_HELP_SEEALSO, "DATE"},
408 { GNM_FUNC_HELP_END }
411 static GnmValue *
412 gnumeric_now (GnmFuncEvalInfo *ei, G_GNUC_UNUSED GnmValue const * const *argv)
414 return value_new_float (go_date_timet_to_serial_raw (time (NULL), DATE_CONV (ei->pos)));
417 /***************************************************************************/
419 static GnmFuncHelp const help_time[] = {
420 { GNM_FUNC_HELP_NAME, F_("TIME:create a time serial value")},
421 { GNM_FUNC_HELP_ARG, F_("hour:hour of the day")},
422 { GNM_FUNC_HELP_ARG, F_("minute:minute within the hour")},
423 { GNM_FUNC_HELP_ARG, F_("second:second within the minute")},
424 { GNM_FUNC_HELP_DESCRIPTION, F_("The TIME function computes the fractional day after midnight at the time given by @{hour}, @{minute}, and @{second}.") },
425 { GNM_FUNC_HELP_NOTE, F_("While the return value is automatically formatted to look like a time between 0:00 and 24:00, "
426 "the underlying serial time value is a number between 0 and 1.")},
427 { GNM_FUNC_HELP_NOTE, F_("If any of @{hour}, @{minute}, and @{second} is negative, #NUM! is returned")},
428 { GNM_FUNC_HELP_EXAMPLES, "=TIME(12,30,2)" },
429 { GNM_FUNC_HELP_EXAMPLES, "=TIME(25,100,18)" },
430 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
431 { GNM_FUNC_HELP_SEEALSO, "ODF.TIME,HOUR,MINUTE,SECOND"},
432 { GNM_FUNC_HELP_END }
435 static GnmValue *
436 gnumeric_time (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
438 gnm_float hours, minutes, seconds;
439 gnm_float time;
441 hours = gnm_fmod (value_get_as_float (argv [0]), 24);
442 minutes = value_get_as_float (argv [1]);
443 seconds = value_get_as_float (argv [2]);
445 if (hours < 0 || minutes < 0 || seconds < 0)
446 return value_new_error_NUM (ei->pos);
448 time = (hours * 3600 + minutes * 60 + seconds) / DAY_SECONDS;
449 time -= gnm_fake_floor (time);
451 return value_new_float (time);
454 /***************************************************************************/
456 static GnmFuncHelp const help_odf_time[] = {
457 { GNM_FUNC_HELP_NAME, F_("ODF.TIME:create a time serial value")},
458 { GNM_FUNC_HELP_ARG, F_("hour:hour")},
459 { GNM_FUNC_HELP_ARG, F_("minute:minute")},
460 { GNM_FUNC_HELP_ARG, F_("second:second")},
461 { GNM_FUNC_HELP_DESCRIPTION, F_("The ODF.TIME function computes the time given by @{hour}, @{minute}, and @{second} as a fraction of a day.") },
462 { GNM_FUNC_HELP_NOTE, F_("While the return value is automatically formatted to look like a time between 0:00 and 24:00, "
463 "the underlying serial time value can be any number.")},
464 { GNM_FUNC_HELP_EXAMPLES, "=ODF.TIME(12,30,2)" },
465 { GNM_FUNC_HELP_EXAMPLES, "=ODF.TIME(25,100,-18)" },
466 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
467 { GNM_FUNC_HELP_SEEALSO, "TIME,HOUR,MINUTE,SECOND"},
468 { GNM_FUNC_HELP_END }
471 static GnmValue *
472 gnumeric_odf_time (G_GNUC_UNUSED GnmFuncEvalInfo *ei, GnmValue const * const *argv)
474 gnm_float hours, minutes, seconds;
476 hours = value_get_as_float (argv [0]);
477 minutes = value_get_as_float (argv [1]);
478 seconds = value_get_as_float (argv [2]);
480 return make_date (value_new_float ((hours * 3600 + minutes * 60 + seconds) /
481 DAY_SECONDS));
484 /***************************************************************************/
486 static GnmFuncHelp const help_timevalue[] = {
487 { GNM_FUNC_HELP_NAME, F_("TIMEVALUE:the time part of a date and time serial value")},
488 { GNM_FUNC_HELP_ARG, F_("serial:date and time serial value")},
489 { GNM_FUNC_HELP_DESCRIPTION, F_("TIMEVALUE returns the time-of-day part of a date and time serial value.") },
490 { GNM_FUNC_HELP_EXAMPLES, "=TIMEVALUE(NOW())" },
491 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
492 { GNM_FUNC_HELP_SEEALSO, "DATEVALUE,TIME"},
493 { GNM_FUNC_HELP_END }
496 static GnmValue *
497 gnumeric_timevalue (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
499 gnm_float raw = datetime_value_to_serial_raw (argv[0], DATE_CONV (ei->pos));
500 return value_new_float (raw - (int)raw);
503 /***************************************************************************/
505 static GnmFuncHelp const help_hour[] = {
506 { GNM_FUNC_HELP_NAME, F_("HOUR:compute hour part of fractional day")},
507 { GNM_FUNC_HELP_ARG, F_("time:time of day as fractional day")},
508 { GNM_FUNC_HELP_DESCRIPTION, F_("The HOUR function computes the hour part of the fractional day given by @{time}.") },
509 { GNM_FUNC_HELP_EXAMPLES, "=HOUR(TIME(12,30,2))" },
510 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
511 { GNM_FUNC_HELP_SEEALSO, "TIME,MINUTE,SECOND"},
512 { GNM_FUNC_HELP_END }
515 static GnmValue *
516 gnumeric_hour (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
518 int secs = datetime_value_to_seconds (argv[0], DATE_CONV (ei->pos));
520 if (secs < 0)
521 return value_new_error_NUM (ei->pos);
522 else
523 return value_new_int (secs / 3600);
526 /***************************************************************************/
528 static GnmFuncHelp const help_minute[] = {
529 { GNM_FUNC_HELP_NAME, F_("MINUTE:compute minute part of fractional day")},
530 { GNM_FUNC_HELP_ARG, F_("time:time of day as fractional day")},
531 { GNM_FUNC_HELP_DESCRIPTION, F_("The MINUTE function computes the minute part of the fractional day given by @{time}.") },
532 { GNM_FUNC_HELP_EXAMPLES, "=MINUTE(TIME(12,30,2))" },
533 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
534 { GNM_FUNC_HELP_SEEALSO, "TIME,HOUR,SECOND"},
535 { GNM_FUNC_HELP_END }
538 static GnmValue *
539 gnumeric_minute (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
541 int secs = datetime_value_to_seconds (argv[0], DATE_CONV (ei->pos));
543 if (secs < 0)
544 return value_new_error_NUM (ei->pos);
545 else
546 return value_new_int (secs / 60 % 60);
549 /***************************************************************************/
551 static GnmFuncHelp const help_second[] = {
552 { GNM_FUNC_HELP_NAME, F_("SECOND:compute seconds part of fractional day")},
553 { GNM_FUNC_HELP_ARG, F_("time:time of day as fractional day")},
554 { GNM_FUNC_HELP_DESCRIPTION, F_("The SECOND function computes the seconds part of the fractional day given by @{time}.") },
555 { GNM_FUNC_HELP_EXAMPLES, "=SECOND(TIME(12,30,2))" },
556 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
557 { GNM_FUNC_HELP_SEEALSO, "TIME,HOUR,MINUTE"},
558 { GNM_FUNC_HELP_END }
561 static GnmValue *
562 gnumeric_second (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
564 int secs = datetime_value_to_seconds (argv[0], DATE_CONV (ei->pos));
566 if (secs < 0)
567 return value_new_error_NUM (ei->pos);
568 else
569 return value_new_int (secs % 60);
572 /***************************************************************************/
574 static GnmFuncHelp const help_year[] = {
575 { GNM_FUNC_HELP_NAME, F_("YEAR:the year part of a date serial value") },
576 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
577 { GNM_FUNC_HELP_DESCRIPTION, F_("The YEAR function returns the year part of @{date}.") },
578 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
579 { GNM_FUNC_HELP_EXAMPLES, "=YEAR(TODAY())" },
580 { GNM_FUNC_HELP_EXAMPLES, "=YEAR(DATE(1940,4,9))" },
581 { GNM_FUNC_HELP_SEEALSO, "DATE,MONTH,DAY"},
582 { GNM_FUNC_HELP_END }
585 static GnmValue *
586 gnumeric_year (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
588 GDate date;
590 if (datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos)))
591 return value_new_int (g_date_get_year (&date));
592 else
593 return value_new_error_NUM (ei->pos);
596 /***************************************************************************/
598 static GnmFuncHelp const help_month[] = {
599 { GNM_FUNC_HELP_NAME, F_("MONTH:the month part of a date serial value") },
600 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
601 { GNM_FUNC_HELP_DESCRIPTION, F_("The MONTH function returns the month part of @{date}.") },
602 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
603 { GNM_FUNC_HELP_EXAMPLES, "=MONTH(TODAY())" },
604 { GNM_FUNC_HELP_EXAMPLES, "=MONTH(DATE(1940,4,9))" },
605 { GNM_FUNC_HELP_SEEALSO, "DATE,YEAR,DAY"},
606 { GNM_FUNC_HELP_END }
609 static GnmValue *
610 gnumeric_month (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
612 GDate date;
614 if (datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos)))
615 return value_new_int (g_date_get_month (&date));
616 else
617 return value_new_error_NUM (ei->pos);
620 /***************************************************************************/
622 static GnmFuncHelp const help_day[] = {
623 { GNM_FUNC_HELP_NAME, F_("DAY:the day-of-month part of a date serial value") },
624 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
625 { GNM_FUNC_HELP_DESCRIPTION, F_("The DAY function returns the day-of-month part of @{date}.") },
626 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
627 { GNM_FUNC_HELP_EXAMPLES, "=DAY(TODAY())" },
628 { GNM_FUNC_HELP_EXAMPLES, "=DAY(DATE(1940,4,9))" },
629 { GNM_FUNC_HELP_SEEALSO, "DATE,YEAR,MONTH"},
630 { GNM_FUNC_HELP_END }
633 static GnmValue *
634 gnumeric_day (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
636 GDate date;
638 if (datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos)))
639 return value_new_int (g_date_get_day (&date));
640 else
641 return value_new_error_NUM (ei->pos);
644 /***************************************************************************/
646 static GnmFuncHelp const help_weekday[] = {
647 { GNM_FUNC_HELP_NAME, F_("WEEKDAY:day-of-week") },
648 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
649 { GNM_FUNC_HELP_ARG, F_("method:numbering system, defaults to 1")},
650 { GNM_FUNC_HELP_DESCRIPTION, F_("The WEEKDAY function returns the day-of-week of @{date}. The value of @{method} determines how days are numbered; it defaults to 1.") },
651 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 1, then Sunday is 1, Monday is 2, etc.") },
652 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 2, then Monday is 1, Tuesday is 2, etc.") },
653 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 3, then Monday is 0, Tuesday is 1, etc.") },
654 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 11, then Monday is 1, Tuesday is 2, etc.") },
655 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 12, then Tuesday is 1, Wednesday is 2, etc.") },
656 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 13, then Wednesday is 1, Thursday is 2, etc.") },
657 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 14, then Thursday is 1, Friday is 2, etc.") },
658 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 15, then Friday is 1, Saturday is 2, etc.") },
659 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 16, then Saturday is 1, Sunday is 2, etc.") },
660 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 17, then Sunday is 1, Monday is 2, etc.") },
661 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
662 { GNM_FUNC_HELP_EXAMPLES, "=WEEKDAY(DATE(1940,4,9))" },
663 { GNM_FUNC_HELP_SEEALSO, "DATE,ISOWEEKNUM"},
664 { GNM_FUNC_HELP_END }
667 static GnmValue *
668 gnumeric_weekday (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
670 GDate date;
671 int res;
672 gnm_float method = argv[1] ? value_get_as_float (argv[1]) : 1;
674 if (method < 1 || method >= G_MAXINT)
675 return value_new_error_NUM (ei->pos);
677 if (!datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos)))
678 return value_new_error_NUM (ei->pos);
680 switch ((int)method) {
681 case 1:
682 case 17:
683 res = (g_date_get_weekday (&date) % 7) + 1;
684 break;
685 case 2:
686 case 11:
687 res = (g_date_get_weekday (&date) + 6) % 7 + 1;
688 break;
689 case 3:
690 res = (g_date_get_weekday (&date) + 6) % 7;
691 break;
692 case 12:
693 res = (g_date_get_weekday (&date) + 5) % 7 + 1;
694 break;
695 case 13:
696 res = (g_date_get_weekday (&date) + 4) % 7 + 1;
697 break;
698 case 14:
699 res = (g_date_get_weekday (&date) + 3) % 7 + 1;
700 break;
701 case 15:
702 res = (g_date_get_weekday (&date) + 2) % 7 + 1;
703 break;
704 case 16:
705 res = (g_date_get_weekday (&date) + 1) % 7 + 1;
706 break;
707 default:
708 return value_new_error_NUM (ei->pos);
711 return value_new_int (res);
714 /***************************************************************************/
716 static GnmFuncHelp const help_days360[] = {
717 { GNM_FUNC_HELP_NAME, F_("DAYS360:days between dates") },
718 { GNM_FUNC_HELP_ARG, F_("start_date:starting date serial value")},
719 { GNM_FUNC_HELP_ARG, F_("end_date:ending date serial value")},
720 { GNM_FUNC_HELP_ARG, F_("method:counting method")},
721 { GNM_FUNC_HELP_DESCRIPTION, F_("DAYS360 returns the number of days from @{start_date} to @{end_date}.") },
722 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 0, the default, the MS Excel (tm) US method will be used. This is a somewhat complicated industry standard method where the last day of February is considered to be the 30th day of the month, but only for @{start_date}.") },
723 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 1, the European method will be used. In this case, if the day of the month is 31 it will be considered as 30") },
724 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 2, a saner version of the US method is used in which both dates get the same February treatment.") },
725 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
726 { GNM_FUNC_HELP_EXAMPLES, "=DAYS360(DATE(2003,2,3),DATE(2007,4,2))" },
727 { GNM_FUNC_HELP_SEEALSO, "DATEDIF"},
728 { GNM_FUNC_HELP_END }
731 static GnmValue *
732 gnumeric_days360 (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
734 go_basis_t basis;
735 GDate date1, date2;
736 GODateConventions const *date_conv = DATE_CONV (ei->pos);
737 gnm_float serial1 = datetime_value_to_serial (argv[0], date_conv);
738 gnm_float serial2 = datetime_value_to_serial (argv[1], date_conv);
739 gnm_float method = argv[2] ? gnm_floor (value_get_as_float (argv[2])) : 0;
741 switch ((int)method) {
742 case 0: basis = GO_BASIS_MSRB_30_360; break;
743 default:
744 case 1: basis = GO_BASIS_30E_360; break;
745 case 2: basis = GO_BASIS_MSRB_30_360_SYM; break;
748 go_date_serial_to_g (&date1, serial1, date_conv);
749 go_date_serial_to_g (&date2, serial2, date_conv);
750 if (!g_date_valid (&date1) || !g_date_valid (&date2))
751 return value_new_error_VALUE (ei->pos);
753 return value_new_int (go_date_days_between_basis (&date1, &date2, basis));
756 /***************************************************************************/
758 static GnmFuncHelp const help_eomonth[] = {
759 { GNM_FUNC_HELP_NAME, F_("EOMONTH:end of month") },
760 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
761 { GNM_FUNC_HELP_ARG, F_("months:signed number of months")},
762 { GNM_FUNC_HELP_DESCRIPTION, F_("EOMONTH returns the date serial value of the end of the month specified by @{date} adjusted forward or backward the number of months specified by @{months}.") },
763 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
764 { GNM_FUNC_HELP_EXAMPLES, "=EOMONTH(DATE(2001,12,14),2)" },
765 { GNM_FUNC_HELP_SEEALSO, "EDATE"},
766 { GNM_FUNC_HELP_END }
769 static GnmValue *
770 gnumeric_eomonth (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
772 gnm_float months = argv[1] ? value_get_as_float (argv[1]) : 0;
773 GDate date;
774 GODateConventions const *conv = DATE_CONV (ei->pos);
776 datetime_value_to_g (&date, argv[0], conv);
777 if (!g_date_valid (&date))
778 return value_new_error_VALUE (ei->pos);
780 if (months > INT_MAX / 2 || -months > INT_MAX / 2)
781 return value_new_error_NUM (ei->pos);
783 gnm_date_add_months (&date, (int)months);
784 if (!g_date_valid (&date) ||
785 g_date_get_year (&date) < 1900 ||
786 g_date_get_year (&date) > 9999)
787 return value_new_error_NUM (ei->pos);
789 g_date_set_day (&date,
790 g_date_get_days_in_month (g_date_get_month (&date),
791 g_date_get_year (&date)));
793 return make_date (value_new_int (go_date_g_to_serial (&date, conv)));
796 /***************************************************************************/
798 static GnmFuncHelp const help_workday[] = {
799 { GNM_FUNC_HELP_NAME, F_("WORKDAY:add working days") },
800 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
801 { GNM_FUNC_HELP_ARG, F_("days:number of days to add")},
802 { GNM_FUNC_HELP_ARG, F_("holidays:array of holidays")},
803 { GNM_FUNC_HELP_ARG, F_("weekend:array of 0s and 1s, indicating whether a weekday "
804 "(S, M, T, W, T, F, S) is on the weekend, defaults to {1,0,0,0,0,0,1}")},
805 { GNM_FUNC_HELP_DESCRIPTION, F_("WORKDAY adjusts @{date} by @{days} skipping over weekends and @{holidays} in the process.") },
806 { GNM_FUNC_HELP_NOTE, F_("@{days} may be negative.") },
807 { GNM_FUNC_HELP_NOTE, F_("If an entry of @{weekend} is non-zero, the corresponding weekday is not a work day.")},
808 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible if the last argument is omitted.") },
809 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
810 { GNM_FUNC_HELP_EXAMPLES, "=WORKDAY(DATE(2001,12,14),2)" },
811 { GNM_FUNC_HELP_EXAMPLES, "=WORKDAY(DATE(2001,12,14),2,,{0,0,0,0,0,1,1})" },
812 { GNM_FUNC_HELP_SEEALSO, "NETWORKDAYS"},
813 { GNM_FUNC_HELP_END }
816 static gint
817 float_compare (gnm_float const *a, gnm_float const *b)
819 if (*a < *b)
820 return -1;
821 else if (*a == *b)
822 return 0;
823 else
824 return 1;
827 static GnmValue *
828 gnumeric_workday (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
830 GDate date;
831 GODateConventions const *conv = DATE_CONV (ei->pos);
832 gnm_float days = value_get_as_float (argv[1]);
833 int idays;
834 gnm_float *holidays = NULL;
835 gnm_float *weekends = NULL;
836 gnm_float const default_weekends[] = {1.,0.,0.,0.,0.,0.,1.};
837 int nholidays, nweekends, n_non_weekend = 0;
838 GDateWeekday weekday;
839 int serial;
840 int i;
842 datetime_value_to_g (&date, argv[0], conv);
843 if (!g_date_valid (&date))
844 goto bad;
846 if (days > INT_MAX / 2 || -days > INT_MAX / 2)
847 return value_new_error_NUM (ei->pos);
848 idays = (int)days;
850 if (argv[3]) {
851 GnmValue *result = NULL;
853 weekends = collect_floats_value (argv[3], ei->pos,
854 COLLECT_COERCE_STRINGS |
855 COLLECT_ZEROONE_BOOLS |
856 COLLECT_IGNORE_BLANKS,
857 &nweekends, &result);
858 if (result)
859 return result;
860 if (nweekends != 7)
861 goto bad;
863 } else {
864 weekends = (gnm_float *)default_weekends;
865 nweekends = 7;
868 for (i = 0; i < 7; i++)
869 if (weekends[i] == 0)
870 n_non_weekend++;
871 if (n_non_weekend == 0 && idays != 0)
872 goto bad;
873 if (n_non_weekend == 0 && idays == 0) {
874 if (weekends != default_weekends)
875 g_free (weekends);
876 return make_date (value_new_int (go_date_g_to_serial (&date, conv)));
879 if (argv[2]) {
880 int j;
881 GDate hol;
882 GnmValue *result = NULL;
884 holidays = collect_floats_value (argv[2], ei->pos,
885 COLLECT_COERCE_STRINGS |
886 COLLECT_IGNORE_BOOLS |
887 COLLECT_IGNORE_BLANKS,
888 &nholidays, &result);
889 if (result) {
890 if (weekends != default_weekends)
891 g_free (weekends);
892 return result;
894 qsort (holidays, nholidays, sizeof (holidays[0]), (void *) &float_compare);
896 for (i = j = 0; i < nholidays; i++) {
897 gnm_float s = holidays[i];
898 int hserial;
899 if (s < 0 || s > INT_MAX)
900 goto bad;
901 hserial = (int)s;
902 if (j > 0 && hserial == holidays[j - 1])
903 continue; /* Dupe */
904 go_date_serial_to_g (&hol, hserial, conv);
905 if (!g_date_valid (&hol))
906 goto bad;
907 if (weekends[g_date_get_weekday (&hol) % 7] != 0.)
908 continue;
909 holidays[j++] = hserial;
911 nholidays = j;
912 } else {
913 holidays = NULL;
914 nholidays = 0;
918 weekday = g_date_get_weekday (&date);
920 if (idays > 0) {
921 int h = 0;
922 guint diff = 0;
923 int old_serial;
925 weekday = weekday % 7;
926 while (weekends[weekday]) {
927 weekday = (weekday > 0) ? (weekday - 1) : G_DATE_SATURDAY;
928 diff++;
930 g_date_subtract_days (&date, diff);
931 old_serial = go_date_g_to_serial (&date, conv);
933 while (idays > 0) {
934 int dm_part_week = idays % n_non_weekend;
935 int ds = idays / n_non_weekend * 7;
937 g_date_add_days (&date, ds);
939 while (dm_part_week) {
940 g_date_add_days (&date, 1);
941 weekday = (weekday + 1) % 7;
942 if (!weekends[weekday])
943 dm_part_week--;
946 serial = go_date_g_to_serial (&date, conv);
948 * we may have passed holidays.
950 idays = 0;
951 while (h < nholidays && holidays[h] <= serial) {
952 if (holidays[h] > old_serial)
953 idays++;
954 h++;
956 old_serial = serial;
958 } else if (idays < 0) {
959 int h = nholidays - 1;
960 guint diff = 0;
961 int old_serial;
963 weekday = weekday % 7;
964 while (weekends[weekday]) {
965 weekday = (weekday + 1) % 7;
966 diff++;
968 g_date_add_days (&date, diff);
969 old_serial = go_date_g_to_serial (&date, conv);
971 idays = -idays;
972 while (idays > 0) {
973 int dm_part_week = idays % n_non_weekend;
974 int ds = idays / n_non_weekend * 7;
976 g_date_subtract_days (&date, ds);
978 while (dm_part_week) {
979 g_date_subtract_days (&date, 1);
980 weekday = (weekday > 0) ? (weekday - 1)
981 : G_DATE_SATURDAY;
982 if (!weekends[weekday])
983 dm_part_week--;
986 serial = go_date_g_to_serial (&date, conv);
988 * we may have passed holidays.
990 idays = 0;
991 while (h >= 0 && holidays[h] >= serial) {
992 if (holidays[h] < old_serial)
993 idays++;
994 h--;
996 old_serial = serial;
998 } else serial = go_date_g_to_serial (&date, conv);
1000 if (weekends != default_weekends)
1001 g_free (weekends);
1002 g_free (holidays);
1004 go_date_serial_to_g (&date, serial, conv);
1005 if (!g_date_valid (&date) ||
1006 g_date_get_year (&date) < 1900 ||
1007 g_date_get_year (&date) > 9999)
1008 return value_new_error_NUM (ei->pos);
1010 return make_date (value_new_int (go_date_g_to_serial (&date, conv)));
1012 bad:
1013 if (weekends != default_weekends)
1014 g_free (weekends);
1015 g_free (holidays);
1016 return value_new_error_VALUE (ei->pos);
1019 /**************************************************************************
1020 networkdays:
1022 in OpenFormula 1.2:
1023 The optional 4th parameter Workdays can be used to specify a different definition for the standard
1024 work week by passing in a list of numbers which define which days of the week are workdays
1025 (indicated by 0) or not (indicated by non-zero) in order Sunday, Monday,...,Saturday. So, the
1026 default definition of the work week excludes Saturday and Sunday and is: {1;0;0;0;0;0;1}. To
1027 define the work week as excluding Friday and Saturday, the third parameter would be:
1028 {0;0;0;0;0;1;1}.
1030 In the implementation, we are using g_date_get_weekday which returns
1031 typedef enum
1033 G_DATE_BAD_WEEKDAY = 0,
1034 G_DATE_MONDAY = 1,
1035 G_DATE_TUESDAY = 2,
1036 G_DATE_WEDNESDAY = 3,
1037 G_DATE_THURSDAY = 4,
1038 G_DATE_FRIDAY = 5,
1039 G_DATE_SATURDAY = 6,
1040 G_DATE_SUNDAY = 7
1041 } GDateWeekday;
1042 Since Sunday here is 7 rather than a 0 we need to make appropriate adjustments.
1044 ***************************************************************************/
1045 static GnmFuncHelp const help_networkdays[] = {
1046 { GNM_FUNC_HELP_NAME, F_("NETWORKDAYS:number of workdays in range") },
1047 { GNM_FUNC_HELP_ARG, F_("start_date:starting date serial value")},
1048 { GNM_FUNC_HELP_ARG, F_("end_date:ending date serial value")},
1049 { GNM_FUNC_HELP_ARG, F_("holidays:array of holidays")},
1050 { GNM_FUNC_HELP_ARG, F_("weekend:array of 0s and 1s, indicating whether a weekday "
1051 "(S, M, T, W, T, F, S) is on the weekend, defaults to {1,0,0,0,0,0,1}")},
1052 { GNM_FUNC_HELP_DESCRIPTION, F_("NETWORKDAYS calculates the number of days from @{start_date} to @{end_date} "
1053 "skipping weekends and @{holidays} in the process.") },
1054 { GNM_FUNC_HELP_NOTE, F_("If an entry of @{weekend} is non-zero, the corresponding weekday is not a work day.")},
1055 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible if the last argument is omitted.") },
1056 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
1057 { GNM_FUNC_HELP_EXAMPLES, "=NETWORKDAYS(DATE(2001,1,2),DATE(2001,2,15))" },
1058 { GNM_FUNC_HELP_EXAMPLES, "=NETWORKDAYS(DATE(2001,1,2),DATE(2001,2,15),,{0, 0, 0, 1, 1, 0, 0})" },
1059 { GNM_FUNC_HELP_SEEALSO, "WORKDAY"},
1060 { GNM_FUNC_HELP_END }
1063 static int
1064 networkdays_calc (GDate start_date, int start_serial, int end_serial,
1065 int n_non_weekend, gnm_float *weekends, int nholidays, gnm_float *holidays)
1067 int res = 0;
1068 int old_start_serial = start_serial;
1069 GDateWeekday weekday;
1070 int i, weeks;
1071 int h = 0;
1073 weekday = g_date_get_weekday (&start_date);
1074 if (weekday == G_DATE_BAD_WEEKDAY)
1075 return -1;
1076 if (weekday == G_DATE_SUNDAY)
1077 weekday = 0;
1079 weeks = (end_serial - start_serial)/7;
1080 start_serial = start_serial + weeks * 7;
1081 res = weeks * n_non_weekend;
1083 for (i = start_serial; i <= end_serial; i++) {
1084 if (!weekends[weekday])
1085 res++;
1086 weekday = (weekday + 1) % 7;
1090 * we may have included holidays.
1093 while (h < nholidays && holidays[h] <= end_serial) {
1094 if (holidays[h] >= old_start_serial)
1095 res--;
1096 h++;
1099 return res;
1102 static GnmValue *
1103 gnumeric_networkdays (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1105 int start_serial;
1106 int end_serial;
1107 int res, total_res = 0;
1108 GDate start_date, trouble_mar, trouble_feb, end_date;
1109 GODateConventions const *conv = DATE_CONV (ei->pos);
1110 gnm_float *holidays = NULL;
1111 gnm_float *weekends = NULL;
1112 gnm_float const default_weekends[] = {1.,0.,0.,0.,0.,0.,1.};
1113 int nholidays, nweekends, n_non_weekend = 0;
1114 int i;
1115 GDateWeekday weekday;
1116 gboolean includes_bad_day = FALSE;
1118 /* Check the date period*/
1120 start_serial = datetime_value_to_serial (argv[0], conv);
1121 end_serial = datetime_value_to_serial (argv[1], conv);
1123 /* Swap if necessary */
1124 if (start_serial > end_serial) {
1125 int tmp = start_serial;
1126 start_serial = end_serial;
1127 end_serial = tmp;
1130 /* Make sure that start and end serial are valid */
1131 if (start_serial <= 0 || end_serial <= 0)
1132 return value_new_error_NUM (ei->pos);
1134 go_date_serial_to_g (&start_date, start_serial, conv);
1135 if (!g_date_valid (&start_date))
1136 goto bad;
1137 go_date_serial_to_g (&end_date, end_serial, conv);
1138 if (!g_date_valid (&end_date))
1139 goto bad;
1141 g_date_set_dmy (&trouble_mar, 1, 3, 1900);
1142 if (g_date_compare (&start_date, &trouble_mar) < 0) {
1143 g_date_set_dmy (&trouble_feb, 28, 2, 1900);
1144 includes_bad_day = (!g_date_valid (&end_date) || g_date_compare (&trouble_feb, &end_date) < 0);
1147 /* get the weekend info */
1149 if (argv[3]) {
1150 GnmValue *result = NULL;
1152 weekends = collect_floats_value (argv[3], ei->pos,
1153 COLLECT_COERCE_STRINGS |
1154 COLLECT_ZEROONE_BOOLS |
1155 COLLECT_IGNORE_BLANKS,
1156 &nweekends, &result);
1157 if (result)
1158 return result;
1159 if (nweekends != 7)
1160 goto bad;
1162 } else {
1163 weekends = (gnm_float *)default_weekends;
1164 nweekends = 7;
1167 /* If everything is a weekend we know the answer already */
1169 for (i = 0; i < 7; i++)
1170 if (weekends[i] == 0)
1171 n_non_weekend++;
1172 if (n_non_weekend == 0) {
1173 if (weekends != default_weekends)
1174 g_free (weekends);
1175 return value_new_int (0);
1178 /* Now get the holiday info */
1180 if (argv[2]) {
1181 int j;
1182 GDate hol;
1183 GnmValue *result = NULL;
1185 holidays = collect_floats_value (argv[2], ei->pos,
1186 COLLECT_COERCE_STRINGS |
1187 COLLECT_IGNORE_BOOLS |
1188 COLLECT_IGNORE_BLANKS,
1189 &nholidays, &result);
1190 if (result) {
1191 if (weekends != default_weekends)
1192 g_free (weekends);
1193 return result;
1195 qsort (holidays, nholidays, sizeof (holidays[0]), (void *) &float_compare);
1197 for (i = j = 0; i < nholidays; i++) {
1198 gnm_float s = holidays[i];
1199 int hserial;
1200 if (s < 0 || s > INT_MAX)
1201 goto bad;
1202 hserial = (int)s;
1203 if (j > 0 && hserial == holidays[j - 1])
1204 continue; /* Dupe */
1205 go_date_serial_to_g (&hol, hserial, conv);
1206 if (!g_date_valid (&hol))
1207 goto bad;
1208 weekday = g_date_get_weekday (&hol);
1209 if (weekday == G_DATE_BAD_WEEKDAY)
1210 goto bad;
1211 if (weekday == G_DATE_SUNDAY)
1212 weekday = 0;
1213 /* We skip holidays that are on the weekend */
1214 if (weekends[weekday] != 0.)
1215 continue;
1216 holidays[j++] = hserial;
1218 nholidays = j;
1219 } else {
1220 holidays = NULL;
1221 nholidays = 0;
1224 if (includes_bad_day) {
1225 total_res = networkdays_calc (start_date, start_serial,
1226 go_date_g_to_serial (&trouble_feb, conv),
1227 n_non_weekend, weekends, nholidays, holidays);
1228 if (total_res < 0)
1229 goto bad;
1230 res = networkdays_calc (trouble_mar, go_date_g_to_serial (&trouble_mar, conv),
1231 end_serial,
1232 n_non_weekend, weekends, nholidays, holidays);
1233 if (res < 0)
1234 goto bad;
1235 total_res += res;
1236 } else {
1237 total_res = networkdays_calc (start_date, start_serial, end_serial,
1238 n_non_weekend, weekends, nholidays, holidays);
1239 if (total_res < 0)
1240 goto bad;
1243 if (weekends != default_weekends)
1244 g_free (weekends);
1245 g_free (holidays);
1247 return value_new_int (total_res);
1249 bad:
1250 if (weekends != default_weekends)
1251 g_free (weekends);
1252 g_free (holidays);
1253 return value_new_error_VALUE (ei->pos);
1256 /***************************************************************************/
1258 static GnmFuncHelp const help_isoweeknum[] = {
1259 { GNM_FUNC_HELP_NAME, F_("ISOWEEKNUM:ISO week number")},
1260 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
1261 { GNM_FUNC_HELP_DESCRIPTION, F_("ISOWEEKNUM calculates the week number according to the ISO 8601 standard. Weeks start on Mondays and week 1 contains the first Thursday of the year.") },
1262 { GNM_FUNC_HELP_NOTE, F_("January 1 of a year is sometimes in week 52 or 53 of the previous year. Similarly, December 31 is sometimes in week 1 of the following year.") },
1263 { GNM_FUNC_HELP_EXAMPLES, "=ISOWEEKNUM(DATE(2000,1,1))" },
1264 { GNM_FUNC_HELP_EXAMPLES, "=ISOWEEKNUM(DATE(2008,1,1))" },
1265 { GNM_FUNC_HELP_SEEALSO, "ISOYEAR,WEEKNUM"},
1266 { GNM_FUNC_HELP_END }
1269 static GnmValue *
1270 gnumeric_isoweeknum (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1272 GDate date;
1273 datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos));
1274 if (!g_date_valid (&date))
1275 return value_new_error_VALUE (ei->pos);
1277 return value_new_int (go_date_weeknum (&date, GO_WEEKNUM_METHOD_ISO));
1280 /***************************************************************************/
1282 static GnmFuncHelp const help_isoyear[] = {
1283 { GNM_FUNC_HELP_NAME, F_("ISOYEAR:year corresponding to the ISO week number")},
1284 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
1285 { GNM_FUNC_HELP_DESCRIPTION, F_("ISOYEAR calculates the year to go with week number according to the ISO 8601 standard.") },
1286 { GNM_FUNC_HELP_NOTE, F_("January 1 of a year is sometimes in week 52 or 53 of the previous year. Similarly, December 31 is sometimes in week 1 of the following year.") },
1287 { GNM_FUNC_HELP_EXAMPLES, "=ISOYEAR(DATE(2000,1,1))" },
1288 { GNM_FUNC_HELP_EXAMPLES, "=ISOYEAR(DATE(2008,1,1))" },
1289 { GNM_FUNC_HELP_SEEALSO, "ISOWEEKNUM,YEAR"},
1290 { GNM_FUNC_HELP_END }
1293 static GnmValue *
1294 gnumeric_isoyear (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1296 GDate date;
1297 int year;
1298 int month;
1299 int isoweeknum;
1301 datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos));
1302 if (!g_date_valid (&date))
1303 return value_new_error_VALUE (ei->pos);
1305 isoweeknum = go_date_weeknum (&date, GO_WEEKNUM_METHOD_ISO);
1306 year = g_date_get_year (&date);
1307 month = g_date_get_month (&date);
1308 if (isoweeknum >= 52 && month == G_DATE_JANUARY)
1309 year--;
1310 else if (isoweeknum == 1 && month == G_DATE_DECEMBER)
1311 year++;
1313 return value_new_int (year);
1316 /***************************************************************************/
1318 static GnmFuncHelp const help_weeknum[] = {
1319 { GNM_FUNC_HELP_NAME, F_("WEEKNUM:week number")},
1320 { GNM_FUNC_HELP_ARG, F_("date:date serial value")},
1321 { GNM_FUNC_HELP_ARG, F_("method:numbering system, defaults to 1")},
1322 { GNM_FUNC_HELP_DESCRIPTION, F_("WEEKNUM calculates the week number according to @{method} which defaults to 1.") },
1323 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 1, then weeks start on Sundays and January 1 is in week 1.") },
1324 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 2, then weeks start on Mondays and January 1 is in week 1.") },
1325 { GNM_FUNC_HELP_NOTE, F_("If @{method} is 150, then the ISO 8601 numbering is used.") },
1326 { GNM_FUNC_HELP_EXAMPLES, "=WEEKNUM(DATE(2000,1,1))" },
1327 { GNM_FUNC_HELP_EXAMPLES, "=WEEKNUM(DATE(2008,1,1))" },
1328 { GNM_FUNC_HELP_SEEALSO, "ISOWEEKNUM"},
1329 { GNM_FUNC_HELP_END }
1332 static GnmValue *
1333 gnumeric_weeknum (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1335 GDate date;
1336 gnm_float method = argv[1] ? gnm_floor (value_get_as_float (argv[1])) : 1;
1337 int m;
1339 if (method == 1)
1340 m = GO_WEEKNUM_METHOD_SUNDAY;
1341 else if (method == 2)
1342 m = GO_WEEKNUM_METHOD_MONDAY;
1343 else if (method == 150 || method == 21)
1344 m = GO_WEEKNUM_METHOD_ISO;
1345 else
1346 return value_new_error_VALUE (ei->pos);
1348 datetime_value_to_g (&date, argv[0], DATE_CONV (ei->pos));
1349 if (!g_date_valid (&date))
1350 return value_new_error_VALUE (ei->pos);
1352 return value_new_int (go_date_weeknum (&date, m));
1355 /***************************************************************************/
1357 static GnmFuncHelp const help_yearfrac[] = {
1358 { GNM_FUNC_HELP_NAME, F_("YEARFRAC:fractional number of years between dates")},
1359 { GNM_FUNC_HELP_ARG, F_("start_date:starting date serial value")},
1360 { GNM_FUNC_HELP_ARG, F_("end_date:ending date serial value")},
1361 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
1362 { GNM_FUNC_HELP_DESCRIPTION, F_("YEARFRAC calculates the number of days from @{start_date} to @{end_date} according to the calendar specified by @{basis}, which defaults to 0, and expresses the result as a fractional number of years.") },
1363 GNM_DATE_BASIS_HELP
1364 { GNM_FUNC_HELP_SEEALSO, "DATE"},
1365 { GNM_FUNC_HELP_EXAMPLES, "=YEARFRAC(DATE(2000,1,1),DATE(2001,4,1))" },
1366 { GNM_FUNC_HELP_END }
1369 static GnmValue *
1370 gnumeric_yearfrac (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1372 GODateConventions const *conv = DATE_CONV (ei->pos);
1373 GDate start_date, end_date;
1374 int basis = value_get_basis (argv[2], GO_BASIS_MSRB_30_360);
1376 if (basis < 0 || basis > 4 ||
1377 !datetime_value_to_g (&start_date, argv[0], conv) ||
1378 !datetime_value_to_g (&end_date, argv[1], conv))
1379 return value_new_error_NUM (ei->pos);
1381 return value_new_float (yearfrac (&start_date, &end_date, basis));
1384 /***************************************************************************/
1386 static GnmFuncHelp const help_days[] = {
1387 { GNM_FUNC_HELP_NAME, F_("DAYS:difference between dates in days") },
1388 { GNM_FUNC_HELP_ARG, F_("end_date:ending date serial value")},
1389 { GNM_FUNC_HELP_ARG, F_("start_date:starting date serial value")},
1390 { GNM_FUNC_HELP_DESCRIPTION, F_("DAYS returns the positive or negative number of days from @{start_date} to @{end_date}.") },
1391 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
1392 { GNM_FUNC_HELP_EXAMPLES, "=DAYS(DATE(2003,2,3),DATE(2007,4,2))" },
1393 { GNM_FUNC_HELP_EXAMPLES, "=DAYS(DATE(2007,4,2),DATE(2003,2,3))" },
1394 { GNM_FUNC_HELP_EXAMPLES, "=DAYS(DATE(1900,2,28),DATE(1900,3,1))" },
1395 { GNM_FUNC_HELP_SEEALSO, "DATEDIF"},
1396 { GNM_FUNC_HELP_END }
1400 static GnmValue *
1401 gnumeric_days (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1403 int date1, date2;
1404 GDate d1, d2;
1405 GODateConventions const *conv = DATE_CONV (ei->pos);
1407 date1 = gnm_floor (value_get_as_float (argv [0]));
1408 date2 = gnm_floor (value_get_as_float (argv [1]));
1410 go_date_serial_to_g (&d1, date1, conv);
1411 go_date_serial_to_g (&d2, date2, conv);
1413 return value_new_int (g_date_days_between (&d2, &d1));
1416 /***************************************************************************/
1418 GnmFuncDescriptor const datetime_functions[] = {
1419 { "date", "fff", help_date,
1420 gnumeric_date, NULL, NULL, NULL,
1421 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
1422 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1423 { "datevalue", "f", help_datevalue,
1424 gnumeric_datevalue, NULL, NULL, NULL,
1425 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1426 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1427 { "datedif", "ffs", help_datedif,
1428 gnumeric_datedif, NULL, NULL, NULL,
1429 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1430 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1431 { "day", "f", help_day,
1432 gnumeric_day, NULL, NULL, NULL,
1433 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1434 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1435 { "days360", "ff|f", help_days360,
1436 gnumeric_days360, NULL, NULL, NULL,
1437 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1438 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1439 { "edate", "ff", help_edate,
1440 gnumeric_edate, NULL, NULL, NULL,
1441 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
1442 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1443 { "eomonth", "f|f", help_eomonth,
1444 gnumeric_eomonth, NULL, NULL, NULL,
1445 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
1446 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1447 { "hour", "f", help_hour,
1448 gnumeric_hour, NULL, NULL, NULL,
1449 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1450 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1451 { "minute", "f", help_minute,
1452 gnumeric_minute, NULL, NULL, NULL,
1453 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1454 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1455 { "month", "f", help_month,
1456 gnumeric_month, NULL, NULL, NULL,
1457 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1458 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1459 { "networkdays", "ff|?A",
1460 help_networkdays, gnumeric_networkdays, NULL, NULL, NULL,
1461 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1462 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1463 { "now", "", help_now,
1464 gnumeric_now, NULL, NULL, NULL,
1465 GNM_FUNC_VOLATILE + GNM_FUNC_AUTO_TIME,
1466 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1467 { "odf.time", "fff", help_odf_time,
1468 gnumeric_odf_time, NULL, NULL, NULL,
1469 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_TIME,
1470 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1471 { "second", "f", help_second,
1472 gnumeric_second, NULL, NULL, NULL,
1473 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1474 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1475 { "time", "fff", help_time,
1476 gnumeric_time, NULL, NULL, NULL,
1477 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_TIME,
1478 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1479 { "timevalue", "f", help_timevalue,
1480 gnumeric_timevalue, NULL, NULL, NULL,
1481 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1482 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1483 { "today", "", help_today,
1484 gnumeric_today, NULL, NULL, NULL,
1485 GNM_FUNC_VOLATILE + GNM_FUNC_AUTO_DATE,
1486 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1487 { "weekday", "f|f", help_weekday,
1488 gnumeric_weekday, NULL, NULL, NULL,
1489 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1490 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1491 { "weeknum", "f|f", help_weeknum,
1492 gnumeric_weeknum, NULL, NULL, NULL,
1493 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1494 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1495 { "workday", "ff|?A", help_workday,
1496 gnumeric_workday, NULL, NULL, NULL,
1497 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
1498 GNM_FUNC_IMPL_STATUS_SUBSET, GNM_FUNC_TEST_STATUS_BASIC },
1499 { "year", "f", help_year,
1500 gnumeric_year, NULL, NULL, NULL,
1501 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1502 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1503 { "yearfrac", "ff|f", help_yearfrac,
1504 gnumeric_yearfrac, NULL, NULL, NULL,
1505 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1506 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1508 { "unix2date", "f", help_unix2date,
1509 gnumeric_unix2date, NULL, NULL, NULL,
1510 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
1511 GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1512 { "date2unix", "f", help_date2unix,
1513 gnumeric_date2unix, NULL, NULL, NULL,
1514 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1515 GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1516 { "isoweeknum", "f", help_isoweeknum,
1517 gnumeric_isoweeknum, NULL, NULL, NULL,
1518 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1519 GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1520 { "isoyear", "f", help_isoyear,
1521 gnumeric_isoyear, NULL, NULL, NULL,
1522 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1523 GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1524 { "days", "ff",
1525 help_days, gnumeric_days, NULL, NULL, NULL,
1526 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
1527 GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1528 {NULL}