http://zorba.io/modules/excel/datetime

View as XML or JSON.

This is a library module offering the same set of functions defined by Microsoft Excel.

Function Summary

date ($year as xs:integer, $month as xs:integer, $day as xs:integer) as xs:date?

Constructs a date given the hours, months and days.

day ($date as xs:date) as xs:integer?

Returns the day of a $date, represented by a serial number.

days360 ($start_date as xs:date, $end_date as xs:date) as xs:integer

Returns the number of days between two dates based on a 360-day year.

days360 ($start_date as xs:date, $end_date as xs:date, $method as xs:boolean) as xs:integer

Returns the number of days between two dates based on a 360-day year.

hour ($time as xs:time) as xs:integer?

Returns the hour of a time value.

minute ($time as xs:time) as xs:integer?

Returns the minutes of a time value.

month ($date as xs:date) as xs:integer?

Returns the month of a $date.

networkdays ($start_date as xs:date, $end_date as xs:date) as xs:integer*

Returns the number of whole working days between $start_date and $end_date.

networkdays ($start_date as xs:date, $end_date as xs:date, $holidays as xs:date*) as xs:integer*

Returns the number of whole working days between $start_date and $end_date.

now () as xs:dateTime?

Returns the current date and time.

second ($time as xs:time) as xs:decimal?

Returns the seconds of a $time value.

time ($hour as xs:integer, $minute as xs:integer, $second as xs:integer) as xs:time?

Constructs a time given the hours, minutes and seconds.

today () as xs:date?

Returns the current date.

weekday ($date as xs:date) as xs:integer?

Returns the day of the week corresponding to a $date.

weekday ($date as xs:date, $return_type as xs:integer) as xs:integer?

Returns the day of the week corresponding to a $date depending on $return_type.

year ($date as xs:date) as xs:integer?

Returns the year corresponding to a date.

Functions

date#3

declare  function excel-datetime:date($year as xs:integer, $month as xs:integer, $day as xs:integer) as xs:date?
Constructs a date given the hours, months and days.

Parameters

year as xs:integer
the year
month as xs:integer
the month
day as xs:integer
the day

Returns

xs:date?
A date given the hours, months and days

day#1

declare  function excel-datetime:day($date as xs:date) as xs:integer?
Returns the day of a $date, represented by a serial number.

Parameters

date as xs:date
the date.

Returns

xs:integer?
The day of a $date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

days360#2

declare  function excel-datetime:days360($start_date as xs:date, $end_date as xs:date) as xs:integer
Returns the number of days between two dates based on a 360-day year.

Parameters

start_date as xs:date
the start date.
end_date as xs:date
the end date.

Returns

xs:integer
The number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations.

Use this function to help compute payments if your accounting system is based on twelve 30-day months.

The metod used is U.S. (NASD). If the starting date is the last day of a month, it becomes equal to the 30th of the same month.

If the ending date is the last day of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.

days360#3

declare  function excel-datetime:days360($start_date as xs:date, $end_date as xs:date, $method as xs:boolean) as xs:integer
Returns the number of days between two dates based on a 360-day year.

Parameters

start_date as xs:date
the start date.
end_date as xs:date
the end date.
method as xs:boolean
if false then US/NASD Method is used, otherwise the European Method is used.

Returns

xs:integer
The number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

The European Method (30E/360)

- If either date A or B falls on the 31st of the month, that date will be changed to the 30th;

- Where date B falls on the last day of February, the actual date B will be used.

The US/NASD Method (30US/360)

- If both date A and B fall on the last day of February, then date B will be changed to the 30th.

- If date A falls on the 31st of a month or last day of February, then date A will be changed to the 30th.

- If date A falls on the 30th of a month after applying (2) above and date B falls on the 31st of a month, then date B will be changed to the 30th.

hour#1

declare  function excel-datetime:hour($time as xs:time) as xs:integer?
Returns the hour of a time value.

Parameters

time as xs:time
the time.

Returns

xs:integer?
The hour of a time value. The hour is as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

minute#1

declare  function excel-datetime:minute($time as xs:time) as xs:integer?
Returns the minutes of a time value.

Parameters

time as xs:time
the time.

Returns

xs:integer?
The minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

month#1

declare  function excel-datetime:month($date as xs:date) as xs:integer?
Returns the month of a $date.

Parameters

date as xs:date
the date.

Returns

xs:integer?
the month of a $date. The month is given as an integer, ranging from 1 (January) to 12 (December).

networkdays#2

declare  function excel-datetime:networkdays($start_date as xs:date, $end_date as xs:date) as xs:integer*
Returns the number of whole working days between $start_date and $end_date.

Parameters

start_date as xs:date
the start date.
end_date as xs:date
the end date.

Returns

xs:integer*
The number of whole working days between start_date and end_date.

Working days exclude weekends and any dates identified in holidays.

Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

networkdays#3

declare  function excel-datetime:networkdays($start_date as xs:date, $end_date as xs:date, $holidays as xs:date*) as xs:integer*
Returns the number of whole working days between $start_date and $end_date.

Parameters

start_date as xs:date
the start date.
end_date as xs:date
the end date.
holidays as xs:date
one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.

Returns

xs:integer*
The number of whole working days between start_date and end_date.

Working days exclude weekends and any dates identified in holidays.

Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

now#0

declare  function excel-datetime:now() as xs:dateTime?
Returns the current date and time.

Parameters

Returns

xs:dateTime?
The current date and time.

second#1

declare  function excel-datetime:second($time as xs:time) as xs:decimal?
Returns the seconds of a $time value.

Parameters

time as xs:time
the time.

Returns

xs:decimal?
The seconds of a $time value. The second is given as an integer in the range 0 (zero) to 59.

time#3

declare  function excel-datetime:time($hour as xs:integer, $minute as xs:integer, $second as xs:integer) as xs:time?
Constructs a time given the hours, minutes and seconds.

Parameters

hour as xs:integer
the hour.
minute as xs:integer
the minute.
second as xs:integer
the second.

Returns

xs:time?
A time given the hours, minutes and seconds.

today#0

declare  function excel-datetime:today() as xs:date?
Returns the current date.

Parameters

Returns

xs:date?
The current date.

weekday#1

declare  function excel-datetime:weekday($date as xs:date) as xs:integer?
Returns the day of the week corresponding to a $date.

Parameters

date as xs:date
the date.

Returns

xs:integer?
The day of the week corresponding to a $date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday).

weekday#2

declare  function excel-datetime:weekday($date as xs:date, $return_type as xs:integer) as xs:integer?
Returns the day of the week corresponding to a $date depending on $return_type.

Parameters

date as xs:date
the date.
return_type as xs:integer
1 for Numbers 1 (Sunday) through 7 (Saturday). 2 for Numbers 1 (Monday) through 7 (Sunday). 3 for Numbers 0 (Monday) through 6 (Sunday).

Returns

xs:integer?
The day of the week corresponding to a $date depending on $return_type.

year#1

declare  function excel-datetime:year($date as xs:date) as xs:integer?
Returns the year corresponding to a date.

Parameters

date as xs:date
the date.

Returns

xs:integer?
The year corresponding to a date.