functions.c 35.3 KB
Newer Older
Jody Goldberg's avatar
Jody Goldberg committed
1
/* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
2 3 4
/*
 * fn-date.c:  Built in date functions.
 *
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
5
 * Authors:
6
 *   Miguel de Icaza (miguel@gnu.org)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
7
 *   Jukka-Pekka Iivonen (iivonen@iki.fi)
Morten Welinder's avatar
Morten Welinder committed
8
 *   Morten Welinder <terra@diku.dk>
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
9 10 11 12 13 14 15 16 17 18 19 20 21 22
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23
 */
24 25 26 27 28 29 30 31
#include <gnumeric-config.h>
#include <gnumeric.h>
#include <func.h>

#include <parse-util.h>
#include <str.h>
#include <cell.h>
#include <datetime.h>
32
#include <value.h>
33
#include <auto-format.h>
Jody Goldberg's avatar
Jody Goldberg committed
34
#include <mathfunc.h>
35

Jody Goldberg's avatar
Jody Goldberg committed
36
#include <math.h>
37 38
#include <string.h>
#include <stdlib.h>
39
#include <libgnome/gnome-i18n.h>
Jody Goldberg's avatar
Jody Goldberg committed
40

Morten Welinder's avatar
Morten Welinder committed
41 42 43 44 45 46
#include "plugin.h"
#include "plugin-util.h"
#include "module-plugin-defs.h"

GNUMERIC_MODULE_PLUGIN_INFO_DECL;

47 48
#define DAY_SECONDS (3600*24)

49
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
50

Morten Welinder's avatar
Morten Welinder committed
51
static const char *help_date = {
52 53
	N_("@FUNCTION=DATE\n"
	   "@SYNTAX=DATE (year,month,day)\n"
54

Arturo Espinosa's avatar
Arturo Espinosa committed
55
	   "@DESCRIPTION="
56
	   "DATE returns the number of days since the 1st of january of 1900"
Arturo Espinosa's avatar
Arturo Espinosa committed
57
	   "(the date serial number) for the given year, month and day.\n"
58
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
59 60 61 62 63 64 65 66
	   "If @month < 1 or @month > 12, the year will be corrected.  A "
	   "similar correction takes place for days.\n"
	   "\n"
	   "The @years should be at least 1900.  If "
	   "@years < 1900, it is assumed to be 1900 + @years.\n"
	   "\n"
	   "If the given date is not valid, DATE returns #NUM! error.\n"
	   "This function is Excel compatible."
Arturo Espinosa's avatar
Arturo Espinosa committed
67
	   "\n"
68
	   "@EXAMPLES=\n"
69
	   "DATE(2001, 3, 30) returns 'Mar 30, 2001'.\n "
70
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
71
	   "@SEEALSO=TODAY, NOW")
72 73 74
};

static Value *
75
gnumeric_date (FunctionEvalInfo *ei, Value **argv)
76 77
{
	int year, month, day;
78
	GDate date;
Morten Welinder's avatar
Morten Welinder committed
79

Morten Welinder's avatar
Morten Welinder committed
80 81 82
	year  = value_get_as_int (argv [0]);
	month = value_get_as_int (argv [1]);
	day   = value_get_as_int (argv [2]);
Morten Welinder's avatar
Morten Welinder committed
83

Morten Welinder's avatar
Morten Welinder committed
84 85 86 87
	if (year < 0 || year > 9999)
		goto error;

	if (year < 1900) /* 1900, not 100.  Ick!  */
Morten Welinder's avatar
Morten Welinder committed
88
		year += 1900;
89

Morten Welinder's avatar
Morten Welinder committed
90
        g_date_clear (&date, 1);
91

Morten Welinder's avatar
Morten Welinder committed
92 93 94
	g_date_set_dmy (&date, 1, 1, year);
	if (!g_date_valid (&date))
		goto error;
95

Morten Welinder's avatar
Morten Welinder committed
96 97 98 99 100 101
	if (month > 0)
		g_date_add_months (&date, month - 1);
	else
		g_date_subtract_months (&date, 1 - month);
	if (!g_date_valid (&date))
		goto error;
102 103

	if (day > 0)
Morten Welinder's avatar
Morten Welinder committed
104
                g_date_add_days (&date, day - 1);
105
	else
Morten Welinder's avatar
Morten Welinder committed
106 107 108
		g_date_subtract_days (&date, 1 - day);
	if (!g_date_valid (&date))
		goto error;
109

Morten Welinder's avatar
Morten Welinder committed
110 111 112
	if (g_date_year (&date) < 1900 || g_date_year (&date) >= 11900)
		goto error;

Morten Welinder's avatar
Morten Welinder committed
113
	return value_new_int (datetime_g_to_serial (&date));
114

Morten Welinder's avatar
Morten Welinder committed
115 116
 error:
	return value_new_error (ei->pos, gnumeric_err_NUM);
117 118
}

119
/***************************************************************************/
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
120

Morten Welinder's avatar
Morten Welinder committed
121
static const char *help_unix2date = {
Morten Welinder's avatar
Morten Welinder committed
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
	N_("@FUNCTION=UNIX2DATE\n"
	   "@SYNTAX=UNIX2DATE(unixtime)\n"

	   "@DESCRIPTION="
	   "UNIX2DATE converts a unix time into a spreadsheet date and time.\n"
	   "\n"
	   "A unix time is the number of seconds since midnight January 1, 1970.\n"
	   "\n"
	   "@EXAMPLES=\n"
	   "\n"
	   "@SEEALSO=NOW, DATE, DATE2UNIX")
};

static Value *
gnumeric_unix2date (FunctionEvalInfo *ei, Value **argv)
{
	gnum_float futime = value_get_as_float (argv [0]);
	time_t utime = (time_t)futime;

	/* Check for overflow.  */
Morten Welinder's avatar
Morten Welinder committed
142
	if (gnumabs (futime - utime) >= 1.0)
Morten Welinder's avatar
Morten Welinder committed
143 144 145 146 147 148 149 150
		return value_new_error (ei->pos, gnumeric_err_VALUE);

	return value_new_float (datetime_timet_to_serial_raw (utime) +
				(futime - utime));
}

/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
151
static const char *help_date2unix = {
Morten Welinder's avatar
Morten Welinder committed
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173
	N_("@FUNCTION=DATE2UNIX\n"
	   "@SYNTAX=DATE2UNIX(serial)\n"

	   "@DESCRIPTION="
	   "DATE2UNIX converts a spreadsheet date and time serial number "
	   "into a unix time.\n"
	   "\n"
	   "A unix time is the number of seconds since midnight January 1, 1970.\n"
	   "\n"
	   "@EXAMPLES=\n"
	   "\n"
	   "@SEEALSO=NOW, DATE, UNIX2DATE")
};

static Value *
gnumeric_date2unix (FunctionEvalInfo *ei, Value **argv)
{
	gnum_float fserial = value_get_as_float (argv [0]);
	int serial = (int)fserial;
	time_t utime = datetime_serial_to_timet (serial);

	/* Check for overflow.  */
Morten Welinder's avatar
Morten Welinder committed
174
	if (gnumabs (fserial - serial) >= 1.0 || utime == (time_t)-1)
Morten Welinder's avatar
Morten Welinder committed
175 176
		return value_new_error (ei->pos, gnumeric_err_VALUE);

Jody Goldberg's avatar
Jody Goldberg committed
177 178
	return value_new_int (utime +
		gnumeric_fake_round (DAY_SECONDS * (fserial - serial)));
Morten Welinder's avatar
Morten Welinder committed
179 180 181 182
}

/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
183
static const char *help_datevalue = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
184
	N_("@FUNCTION=DATEVALUE\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
185
	   "@SYNTAX=DATEVALUE(date_str)\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
186 187 188

	   "@DESCRIPTION="
	   "DATEVALUE returns the serial number of the date.  @date_str is "
189
	   "the string that contains the date.\n"
190
	   "This function is Excel compatible. "
Morten Welinder's avatar
Morten Welinder committed
191
	   "\n"
192
	   "@EXAMPLES=\n"
193
	   "DATEVALUE(\"1/1/1999\") equals 36161."
194
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
195 196 197 198
	   "@SEEALSO=DATE")
};

static Value *
199
gnumeric_datevalue (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
200
{
201 202
	if (argv[0]->type == VALUE_ERROR)
		return value_duplicate (argv[0]);
Morten Welinder's avatar
Morten Welinder committed
203
	return value_new_int (datetime_value_to_serial (argv[0]));
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
204 205
}

206 207
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
208
static const char *help_datedif = {
209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
	N_("@FUNCTION=DATEDIF\n"
	   "@SYNTAX=DATEDIF(date1,date2,interval)\n"

	   "@DESCRIPTION="
	   "DATEDIF returns the difference between two dates.  @interval is "
	   "one of six possible values:  \"y\", \"m\", \"d\", \"ym\", "
	   "\"md\", and \"yd\".\n"
	   "The first three options will return the "
	   "number of complete years, months, or days, respectively, between "
	   "the two dates specified.\n"
	   "\"ym\" will return the number of full months between the two "
	   "dates, not including the difference in years.\n"
	   "\"md\" will return the number of full days between the two "
	   "dates, not including the difference in months.\n"
	   "\"yd\" will return the number of full days between the two "
	   "dates, not including the difference in years.\n"
225
	   "This function is Excel compatible. "
226 227
	   "\n"
	   "@EXAMPLES=\n"
228 229
	   "DATEDIF(DATE(2000,4,30),DATE(2003,8,4),\"d\") equals 1191.\n"
	   "DATEDIF(DATE(2000,4,30),DATE(2003,8,4),\"y\") equals 3.\n"
230 231 232 233
	   "\n"
	   "@SEEALSO=DATE")
};

234
static int
235 236 237 238 239 240 241 242
datedif_opt_ym (GDate *gdate1, GDate *gdate2)
{
	g_assert (g_date_valid (gdate1));
	g_assert (g_date_valid (gdate2));

	return datetime_g_months_between (gdate1, gdate2) % 12;
}

243
static int
244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261
datedif_opt_yd (GDate *gdate1, GDate *gdate2, int excel_compat)
{
	int day;

	g_assert (g_date_valid (gdate1));
	g_assert (g_date_valid (gdate2));

	day = g_date_day (gdate1);

	g_date_add_years (gdate1,
	                       datetime_g_years_between (gdate1, gdate2));
	/* according to glib.h, feb 29 turns to feb 28 if necessary */

	if (excel_compat) {
		int new_year1, new_year2;

		/* treat all years divisible by four as leap years: */
		/* this is clearly wrong, but it's what Excel does. */
Morten Welinder's avatar
Morten Welinder committed
262
		/* (I use 2004 here since it is clearly a leap year.) */
263
		new_year1 = 2004 + (g_date_year (gdate1) & 0x3);
Jody Goldberg's avatar
Jody Goldberg committed
264
		new_year2 = new_year1 + (g_date_year (gdate2) -
265
					 g_date_year (gdate1));
266 267 268
		g_date_set_year (gdate1, new_year1);
		g_date_set_year (gdate2, new_year2);

269 270 271
		{
			static gboolean need_warning = TRUE;
			if (need_warning) {
272 273
				g_warning("datedif is known to differ from Excel "
					  "for some values.");
274 275 276
				need_warning = FALSE;
			}
		}
277 278 279 280 281
	}

	return datetime_g_days_between (gdate1, gdate2);
}

282
static int
283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300
datedif_opt_md (GDate *gdate1, GDate *gdate2, int excel_compat)
{
	int day;

	g_assert (g_date_valid (gdate1));
	g_assert (g_date_valid (gdate2));

	day = g_date_day (gdate1);

	g_date_add_months (gdate1,
	                   datetime_g_months_between (gdate1, gdate2));
	/* according to glib.h, days>28 decrease if necessary */

	if (excel_compat) {
		int new_year1, new_year2;

		/* treat all years divisible by four as leap years: */
		/* this is clearly wrong, but it's what Excel does. */
Morten Welinder's avatar
Morten Welinder committed
301
		/* (I use 2004 here since it is clearly a leap year.) */
302
		new_year1 = 2004 + (g_date_year (gdate1) & 0x3);
303 304
		new_year2 = new_year1 + (g_date_year (gdate2) -
					 g_date_year (gdate1));
305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322
		g_date_set_year (gdate1, new_year1);
		g_date_set_year (gdate2, new_year2);

		/* add back the days if they were decreased by
		   g_date_add_months */
		/* ( i feel this is inferior because it reports e.g.:
		     datedif(1/31/95,3/1/95,"d") == -2 ) */
		g_date_add_days (gdate1,
		                 day - g_date_day (gdate1));
	}

	return datetime_g_days_between (gdate1, gdate2);
}

static Value *
gnumeric_datedif (FunctionEvalInfo *ei, Value **argv)
{
	int date1, date2;
323
	const char *opt;
324 325 326 327 328 329

	GDate *gdate1, *gdate2;
	Value *result;

	date1 = floor (value_get_as_float (argv [0]));
	date2 = floor (value_get_as_float (argv [1]));
Morten Welinder's avatar
Morten Welinder committed
330
	opt = value_peek_string (argv[2]);
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366

	if (date1 > date2) {
		return value_new_error (ei->pos, gnumeric_err_NUM);
	}

	if (!strcmp (opt, "d")) {
		return value_new_int (date2 - date1);
	}

	gdate1 = datetime_serial_to_g (date1);
	gdate2 = datetime_serial_to_g (date2);

	if (!g_date_valid (gdate1) || !g_date_valid (gdate2)) {
		result = value_new_error (ei->pos, gnumeric_err_VALUE);
	} else {
		if (!strcmp (opt, "m")) {
			result = value_new_int (
				datetime_g_months_between (gdate1, gdate2));
		} else if (!strcmp (opt, "y")) {
			result = value_new_int (
				datetime_g_years_between (gdate1, gdate2));
		} else if (!strcmp (opt, "ym")) {
			result = value_new_int (
				datedif_opt_ym (gdate1, gdate2));
		} else if (!strcmp (opt, "yd")) {
			result = value_new_int (
				datedif_opt_yd (gdate1, gdate2, 1));
		} else if (!strcmp (opt, "md")) {
			result = value_new_int (
				datedif_opt_md (gdate1, gdate2, 1));
		} else {
			result = value_new_error (
				ei->pos, gnumeric_err_VALUE);
		}
	}

367 368
	datetime_g_free (gdate1);
	datetime_g_free (gdate2);
369 370 371 372 373 374

	return result;
}

/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
375
static const char *help_edate = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
376
	N_("@FUNCTION=EDATE\n"
377
	   "@SYNTAX=EDATE(date,months)\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
378 379 380 381 382 383

	   "@DESCRIPTION="
	   "EDATE returns the serial number of the date that is the "
	   "specified number of months before or after a given date.  "
	   "@date is the serial number of the initial date and @months "
	   "is the number of months before (negative number) or after "
384
	   "(positive number) the initial date.\n"
385
	   "This function is Excel compatible. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
386
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
387
	   "If @months is not an integer, it is truncated."
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
388
	   "\n"
389
	   "@EXAMPLES=\n"
390
	   "EDATE(DATE(2001,12,30),2) returns 'Feb 28, 2002'.\n"
391
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
392 393 394 395
	   "@SEEALSO=DATE")
};

static Value *
396
gnumeric_edate (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
397 398 399
{
	int    serial, months;
	GDate* date;
400
	Value *res;
Morten Welinder's avatar
Morten Welinder committed
401

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
402 403 404
	serial = value_get_as_int(argv[0]);
	months = value_get_as_int(argv[1]);

Morten Welinder's avatar
Morten Welinder committed
405
	date = datetime_serial_to_g (serial);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
406

407
	if (!g_date_valid (date)) {
408
                  datetime_g_free (date);
409
                  return value_new_error (ei->pos, gnumeric_err_VALUE);
410
	}
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
411 412 413 414 415 416

	if (months > 0)
	        g_date_add_months (date, months);
	else
	        g_date_subtract_months (date, -months);

417
	if (!g_date_valid (date)) {
418
                  datetime_g_free (date);
419
                  return value_new_error (ei->pos, gnumeric_err_NUM);
420
	}
Morten Welinder's avatar
Morten Welinder committed
421

Morten Welinder's avatar
Morten Welinder committed
422
	res = value_new_int (datetime_g_to_serial (date));
423
	datetime_g_free (date);
424
	return res;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
425 426
}

427 428
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
429
static const char *help_today = {
430
	N_("@FUNCTION=TODAY\n"
431
	   "@SYNTAX=TODAY()\n"
432

Arturo Espinosa's avatar
Arturo Espinosa committed
433
	   "@DESCRIPTION="
434
	   "TODAY returns the serial number for today (the number of days "
435
	   "elapsed since the 1st of January of 1900).\n"
436
	   "This function is Excel compatible. "
Arturo Espinosa's avatar
Arturo Espinosa committed
437
	   "\n"
438
	   "@EXAMPLES=\n"
439
	   "TODAY() returns 'Nov 6, 2001' on that particular day.\n "
440
	   "\n"
441
	   "@SEEALSO=NOW")
442 443 444
};

static Value *
445
gnumeric_today (FunctionEvalInfo *ei, Value **argv)
446
{
Morten Welinder's avatar
Morten Welinder committed
447
	return value_new_int (datetime_timet_to_serial (time (NULL)));
448 449
}

450 451
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
452
static const char *help_now = {
453 454
	N_("@FUNCTION=NOW\n"
	   "@SYNTAX=NOW ()\n"
455

Arturo Espinosa's avatar
Arturo Espinosa committed
456
	   "@DESCRIPTION="
457
	   "NOW returns the serial number for the date and time at the time "
Arturo Espinosa's avatar
Arturo Espinosa committed
458
	   "it is evaluated.\n"
459 460 461
	   ""
	   "Serial Numbers in Gnumeric are represented as follows:"
	   "The integral part is the number of days since the 1st of "
462
	   "January of 1900.  The decimal part represent the fraction "
463
	   "of the day and is mapped into hour, minutes and seconds.\n"
464 465
	   ""
	   "For example: .0 represents the beginning of the day, and 0.5 "
466
	   "represents noon.\n"
467
	   "This function is Excel compatible. "
Arturo Espinosa's avatar
Arturo Espinosa committed
468
	   "\n"
469
	   "@EXAMPLES=\n"
470
	   "NOW().\n"
471
	   "\n"
472
	   "@SEEALSO=TODAY")
473 474
};

475 476 477
static Value *
gnumeric_now (FunctionEvalInfo *ei, Value **argv)
{
Morten Welinder's avatar
Morten Welinder committed
478
	return value_new_float (datetime_timet_to_serial_raw (time (NULL)));
479 480
}

481 482
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
483
static const char *help_time = {
484 485
	N_("@FUNCTION=TIME\n"
	   "@SYNTAX=TIME (hours,minutes,seconds)\n"
486

Arturo Espinosa's avatar
Arturo Espinosa committed
487
	   "@DESCRIPTION="
488
	   "TIME returns a fraction representing the time of day.\n"
489
	   "This function is Excel compatible. "
Arturo Espinosa's avatar
Arturo Espinosa committed
490
	   "\n"
491
	   "@EXAMPLES=\n"
492
	   "TIME(3, 5, 23) equals 3:05AM.\n"
493
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
494
	   "@SEEALSO=HOUR")
495 496 497
};

static Value *
498
gnumeric_time (FunctionEvalInfo *ei, Value **argv)
499
{
Jody Goldberg's avatar
Jody Goldberg committed
500
	gnum_float hours, minutes, seconds;
501

Michael Meeks's avatar
Michael Meeks committed
502 503 504
	hours   = value_get_as_float (argv [0]);
	minutes = value_get_as_float (argv [1]);
	seconds = value_get_as_float (argv [2]);
505

506 507
	return value_new_float ((hours * 3600 + minutes * 60 + seconds) /
				DAY_SECONDS);
Morten Welinder's avatar
Morten Welinder committed
508
}
509

510
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
511

Morten Welinder's avatar
Morten Welinder committed
512
static const char *help_timevalue = {
Morten Welinder's avatar
Morten Welinder committed
513
	N_("@FUNCTION=TIMEVALUE\n"
Morten Welinder's avatar
Morten Welinder committed
514
	   "@SYNTAX=TIMEVALUE (timetext)\n"
Morten Welinder's avatar
Morten Welinder committed
515 516

	   "@DESCRIPTION="
517
	   "TIMEVALUE returns a fraction representing the time of day, a number "
518
	   "between 0 and 1.\n"
519
	   "This function is Excel compatible. "
Morten Welinder's avatar
Morten Welinder committed
520
	   "\n"
521
	   "@EXAMPLES=\n"
Jody Goldberg's avatar
Jody Goldberg committed
522
	   "TIMEVALUE(\"3:05\") equals 0.128472.\n"
523
	   "TIMEVALUE(\"2:24:53 PM\") equals 0.600613.\n"
524
	   "\n"
525
	   "@SEEALSO=HOUR,MINUTE")
Morten Welinder's avatar
Morten Welinder committed
526 527 528
};

static Value *
529
gnumeric_timevalue (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
530
{
Jody Goldberg's avatar
Jody Goldberg committed
531
	gnum_float raw;
532 533 534
	if (argv[0]->type == VALUE_ERROR)
		return value_duplicate (argv[0]);

Morten Welinder's avatar
Morten Welinder committed
535 536
	raw = datetime_value_to_serial_raw (argv[0]);
	return value_new_float (raw - (int)raw);
537 538
}

539
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
540

Morten Welinder's avatar
Morten Welinder committed
541
static const char *help_hour = {
542 543
	N_("@FUNCTION=HOUR\n"
	   "@SYNTAX=HOUR (serial_number)\n"
544

Arturo Espinosa's avatar
Arturo Espinosa committed
545
	   "@DESCRIPTION="
546
	   "HOUR converts a serial number to an hour.  The hour is returned as "
547
	   "an integer in the range 0 (12:00 A.M.) to 23 (11:00 P.M.)."
Arturo Espinosa's avatar
Arturo Espinosa committed
548
	   "\n"
549 550
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
Morten Welinder's avatar
Morten Welinder committed
551
	   "string.\n"
552
	   "This function is Excel compatible. "
553 554
	   "\n"
	   "@EXAMPLES=\n"
555
	   "HOUR(0.128472) equals 3.\n"
556
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
557
	   "@SEEALSO=MINUTE, NOW, TIME, SECOND")
558 559
};

Morten Welinder's avatar
Morten Welinder committed
560
static Value *
561
gnumeric_hour (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
562 563
{
	int secs;
Morten Welinder's avatar
Morten Welinder committed
564
	secs = datetime_value_to_seconds (argv[0]);
Morten Welinder's avatar
Morten Welinder committed
565 566 567
	return value_new_int (secs / 3600);
}

568
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
569

Morten Welinder's avatar
Morten Welinder committed
570
static const char *help_minute = {
571 572
	N_("@FUNCTION=MINUTE\n"
	   "@SYNTAX=MINUTE (serial_number)\n"
573

Arturo Espinosa's avatar
Arturo Espinosa committed
574
	   "@DESCRIPTION="
575 576
	   "MINUTE converts a serial number to a minute.  The minute is returned "
	   "as an integer in the range 0 to 59."
Arturo Espinosa's avatar
Arturo Espinosa committed
577
	   "\n"
578 579
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
580
	   "string.\n"
581
	   "This function is Excel compatible. "
582 583
	   "\n"
	   "@EXAMPLES=\n"
584
	   "MINUTE(0.128472) equals 5.\n"
585
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
586
	   "@SEEALSO=HOUR, NOW, TIME, SECOND")
587 588
};

Morten Welinder's avatar
Morten Welinder committed
589
static Value *
590
gnumeric_minute (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
591 592 593
{
	int secs;

Morten Welinder's avatar
Morten Welinder committed
594
	secs = datetime_value_to_seconds (argv[0]);
Morten Welinder's avatar
Morten Welinder committed
595 596 597
	return value_new_int ((secs / 60) % 60);
}

598
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
599

Morten Welinder's avatar
Morten Welinder committed
600
static const char *help_second = {
601 602
	N_("@FUNCTION=SECOND\n"
	   "@SYNTAX=SECOND (serial_number)\n"
603

Arturo Espinosa's avatar
Arturo Espinosa committed
604
	   "@DESCRIPTION="
605 606
	   "SECOND converts a serial number to a second.  The second is returned "
	   "as an integer in the range 0 to 59."
Arturo Espinosa's avatar
Arturo Espinosa committed
607
	   "\n"
608 609
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
610
	   "string.\n"
611
	   "This function is Excel compatible. "
612 613
	   "\n"
	   "@EXAMPLES=\n"
614
	   "SECOND(0.600613) equals 53.\n"
615
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
616
	   "@SEEALSO=HOUR, MINUTE, NOW, TIME")
617 618 619
};

static Value *
620
gnumeric_second (FunctionEvalInfo *ei, Value **argv)
621
{
Morten Welinder's avatar
Morten Welinder committed
622 623
	int secs;

Morten Welinder's avatar
Morten Welinder committed
624
	secs = datetime_value_to_seconds (argv[0]);
Morten Welinder's avatar
Morten Welinder committed
625
	return value_new_int (secs % 60);
626 627
}

628
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
629

Morten Welinder's avatar
Morten Welinder committed
630
static const char *help_year = {
631 632
	N_("@FUNCTION=YEAR\n"
	   "@SYNTAX=YEAR (serial_number)\n"
633

Arturo Espinosa's avatar
Arturo Espinosa committed
634
	   "@DESCRIPTION="
635
	   "YEAR converts a serial number to a year."
Arturo Espinosa's avatar
Arturo Espinosa committed
636
	   "\n"
637 638
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
639
	   "string.\n"
640
	   "This function is Excel compatible. "
641 642
	   "\n"
	   "@EXAMPLES=\n"
643
	   "YEAR(DATE(2003, 4, 30)) equals 2003.\n"
644
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
645
	   "@SEEALSO=DAY, MONTH, TIME, NOW")
646 647
};

Morten Welinder's avatar
Morten Welinder committed
648
static Value *
649
gnumeric_year (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
650
{
651
	int res = 1900;
652 653 654 655 656 657
	GDate *date;

	if (argv[0]->type == VALUE_ERROR)
		return value_duplicate (argv[0]);

	date = datetime_value_to_g (argv[0]);
658
	if (date != NULL) {
659 660
		res = g_date_year (date);
		g_date_free (date);
661
	}
Morten Welinder's avatar
Morten Welinder committed
662 663 664
	return value_new_int (res);
}

665
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
666

Morten Welinder's avatar
Morten Welinder committed
667
static const char *help_month = {
668 669
	N_("@FUNCTION=MONTH\n"
	   "@SYNTAX=MONTH (serial_number)\n"
670

Arturo Espinosa's avatar
Arturo Espinosa committed
671
	   "@DESCRIPTION="
672
	   "MONTH converts a serial number to a month."
Arturo Espinosa's avatar
Arturo Espinosa committed
673
	   "\n"
674 675
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
676
	   "string.\n"
677
	   "This function is Excel compatible. "
678 679
	   "\n"
	   "@EXAMPLES=\n"
680
	   "MONTH(DATE(2003, 4, 30)) equals 4.\n"
681
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
682
	   "@SEEALSO=DAY, TIME, NOW, YEAR")
683 684
};

Morten Welinder's avatar
Morten Welinder committed
685
static Value *
686
gnumeric_month (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
687
{
688
	int res = 1;
689
	GDate *date;
690

691 692 693 694
	if (argv[0]->type == VALUE_ERROR)
		return value_duplicate (argv[0]);

	date = datetime_value_to_g (argv[0]);
695
	if (date != NULL) {
696 697
		res = g_date_month (date);
		g_date_free (date);
698
	}
Morten Welinder's avatar
Morten Welinder committed
699 700 701
	return value_new_int (res);
}

702
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
703

Morten Welinder's avatar
Morten Welinder committed
704
static const char *help_day = {
705
	N_("@FUNCTION=DAY\n"
Miguel de Icaza's avatar
New:  
Miguel de Icaza committed
706
	   "@SYNTAX=DAY (serial_number)\n"
707

Arturo Espinosa's avatar
Arturo Espinosa committed
708
	   "@DESCRIPTION="
709
	   "DAY converts a serial number to a day of month."
Arturo Espinosa's avatar
Arturo Espinosa committed
710
	   "\n"
711 712
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
713
	   "string.\n"
714
	   "This function is Excel compatible. "
715 716
	   "\n"
	   "@EXAMPLES=\n"
717
	   "DAY(\"10/24/1968\") equals 24.\n"
718
	   "\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
719
	   "@SEEALSO=MONTH, TIME, NOW, YEAR")
720 721 722
};

static Value *
723
gnumeric_day (FunctionEvalInfo *ei, Value **argv)
724
{
725
	int res = 1;
726 727 728 729 730 731
	GDate *date;

	if (argv[0]->type == VALUE_ERROR)
		return value_duplicate (argv[0]);

	date = datetime_value_to_g (argv[0]);
732
	if (date != NULL) {
733 734
		res = g_date_day (date);
		g_date_free (date);
735
	}
Morten Welinder's avatar
Morten Welinder committed
736 737
	return value_new_int (res);
}
738

739
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
740

Morten Welinder's avatar
Morten Welinder committed
741
static const char *help_weekday = {
Morten Welinder's avatar
Morten Welinder committed
742
	N_("@FUNCTION=WEEKDAY\n"
Morten Welinder's avatar
Morten Welinder committed
743
	   "@SYNTAX=WEEKDAY (serial_number[, method])\n"
Morten Welinder's avatar
Morten Welinder committed
744 745

	   "@DESCRIPTION="
746
	   "WEEKDAY converts a serial number to a weekday.\n"
747
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
748 749 750 751 752 753
	   "This function returns an integer indicating the day of week.\n"
	   "@METHOD indicates the numbering system.  It defaults to 1.\n"
	   "\n"
	   "For @METHOD=1: Sunday is 1, Monday is 2, etc.\n"
	   "For @METHOD=2: Monday is 1, Tuesday is 2, etc.\n"
	   "For @METHOD=3: Monday is 0, Tuesday is 1, etc.\n"
Morten Welinder's avatar
Morten Welinder committed
754 755 756
	   "\n"
	   "Note that Gnumeric will perform regular string to serial "
	   "number conversion for you, so you can enter a date as a "
757
	   "string.\n"
758
	   "This function is Excel compatible. "
759 760
	   "\n"
	   "@EXAMPLES=\n"
761
	   "WEEKDAY(\"10/24/1968\") equals 5 (Thursday).\n"
762
	   "\n"
763
	   "@SEEALSO=DAY, MONTH, TIME, NOW, YEAR")
Morten Welinder's avatar
Morten Welinder committed
764 765 766
};

static Value *
767
gnumeric_weekday (FunctionEvalInfo *ei, Value **argv)
Morten Welinder's avatar
Morten Welinder committed
768
{
Morten Welinder's avatar
Morten Welinder committed
769
	int res;
770
	GDate *date;
Morten Welinder's avatar
Morten Welinder committed
771
	int method = argv[1] ? value_get_as_int (argv[1]) : 1;
772

Morten Welinder's avatar
Morten Welinder committed
773 774
	if (method < 1 || method > 3)
		return value_new_error (ei->pos, gnumeric_err_VALUE);
775 776

	date = datetime_value_to_g (argv[0]);
Morten Welinder's avatar
Morten Welinder committed
777 778 779
	if (!date)
		return value_new_error (ei->pos, gnumeric_err_VALUE);

Morten Welinder's avatar
Morten Welinder committed
780 781 782 783 784 785 786
	switch (method) {
	case 1: res = (g_date_weekday (date) % 7) + 1; break;
	case 2: res = (g_date_weekday (date) + 6) % 7 + 1; break;
	case 3: res = (g_date_weekday (date) + 6) % 7; break;
	default: abort ();
	}

Morten Welinder's avatar
Morten Welinder committed
787 788
	g_date_free (date);

Morten Welinder's avatar
Morten Welinder committed
789
	return value_new_int (res);
790 791
}

792
/***************************************************************************/
Morten Welinder's avatar
Morten Welinder committed
793

Morten Welinder's avatar
Morten Welinder committed
794
static const char *help_days360 = {
795
	N_("@FUNCTION=DAYS360 \n"
Morten Welinder's avatar
Morten Welinder committed
796
	   "@SYNTAX=DAYS360 (date1,date2,method)\n"
Morten Welinder's avatar
Morten Welinder committed
797 798

	   "@DESCRIPTION="
799
	   "DAYS360 returns the number of days from @date1 to @date2 following a "
Morten Welinder's avatar
Morten Welinder committed
800 801
	   "360-day calendar in which all months are assumed to have 30 days."
	   "\n"
802
	   "If @method is true, the European method will be used.  In this "
Morten Welinder's avatar
Morten Welinder committed
803 804
	   "case, if the day of the month is 31 it will be considered as 30."
	   "\n"
805
	   "If @method is false or omitted, the US method will be used.  "