xls.datetime {xlsReadWrite} | R Documentation |
Misc. functions to convert Excel datetime values to and from strings.
dateTimeToStr( odate, format = "" ) strToDateTime( sdate ) dateTimeToIsoStr( odate, isoformat = "YYYY-MM-DD hh:mm:ss" ) isoStrToDateTime( sdate )
odate |
a numeric (double) datetime value from Excel. |
format |
formatting string. See list in details. |
isoformat |
one of the following character strings: YYYYMMDD (basic date),
YYYY-MM-DD (extended date), YYYYMMDDhhmmss (basic date/time),
YYYY-MM-DD hh:mm:ss (extended date/time) or YYYY-MM-DD hh:mm:ss.f
(extended date/time including fractions (with 1, 2 or 3 decimal places) |
sdate |
a date as a string. |
dateTimeToStr
converts a given double value to a string representation.
Optionally a formatting string can be used. By default the shortDateFormat
and longTimeFormat settings of the system will be used.
strToDateTime
converts a given character string to a double value.
The string must contain a valid date and/or time value (in respect of the
current locale).
dateTimeToIsoStr
and isoStrToDateTime
do the same, but work with
date strings that are formatted according to the isoformat.
The following table lists the supported formatting strings (adapted from Delphi help):
c | ShortDateFormat followed by LongTimeFormat. Time is not displayed if midnight precisely. |
d | day as a number without a leading zero (1-31). |
dd | day as a number with a leading zero (01-31). |
ddd | day as an abbreviation (Sun-Sat) using the ShortDayNames global variable. |
dddd | day as a full name (Sunday-Saturday) using LongDayNames. |
ddddd | date using ShortDateFormat variable. |
dddddd | date using LongDateFormat. |
e | year in the current period/era as a number without a leading zero. |
ee | year in the current period/era as a number with a leading zero. |
g | period/era as an abbreviation. |
gg | period/era as a full name. |
m | month as a number without a leading zero (1-12). If m immediately follows an h or hh, the minute rather than the month is displayed. |
mm | month as a number with a leading zero (01-12). |
mmm | month as an abbreviation (Jan-Dec) using the ShortMonthNames global variable. |
mmmm | month as a full name (January-December) using LongMonthNames. |
yy | year as a two-digit number (00-99). |
yyyy | year as a four-digit number (0000-9999). |
h | hour without a leading zero (0-23). |
hh | hour with a leading zero (00-23). |
n | minute without a leading zero (0-59). |
nn | minute with a leading zero (00-59). |
s | second without a leading zero (0-59). |
ss | second with a leading zero (00-59). |
z | millisecond without a leading zero (0-999). |
zzz | millisecond with a leading zero (000-999). |
t | time using the format given by ShortTimeFormat. |
tt | time using the format given by LongTimeFormat. |
am/pm | uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly. |
a/p | uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly. |
ampm | uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString global variable for any hour before noon, and the contents of the TimePMString global variable for any hour after noon. |
/ | separator character given by the DateSeparator variable. |
: | time separator character given by TimeSeparator. |
'xx'/"xx" | characters enclosed in single or double quotes are displayed as-is, and do not affect formatting. |
While the free version should be sufficient for most day-to-day duties, you have
the opportunity to choose a more powerful pro version and at the same time support
our development and maintenance effort. Additional features of xlsReadWritePro:
- work with data area subsets, - use (named) ranges and pick individual cells,
- work with sheets (copy, rename, delete), - read/write formula values,
- insert images, - use oleDateTime classes and many datetime helper functions,
- append data to existing files and - work with in-memory objects (e.g. to compose Excel reports).
The trial (http://treetron.googlepages.com) is free and the two versions can easily be switched and coexist just fine. So, why not check it out for yourself?
Hans-Peter Suter