http://www.zorba-xquery.com/modules/excel/datetime

import module namespace excel-datetime = "http://www.zorba-xquery.com/modules/excel/datetime";

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

Sorin Nasoi

xquery version "3.0" encoding "utf-8";

• the XQuery module can be found here.

Imported modules:

• http://www.zorba-xquery.com/modules/excel/text

For more details please also see:

• Excel Documentation: Datetime Functions

date

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.

• $year the year
• $month the month
• $day the day

• A date given the hours, months and days

• http://office.microsoft.com/en-us/excel/HP052090421033.aspxs

day

declare function excel-datetime:day (
            $date as xs:date 
 ) as xs:integer?

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

• $date the date.

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

• http://office.microsoft.com/en-us/excel/HP052090461033.aspx

days360

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.

• $start_date the start date.
• $end_date the end date.

• 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.

• http://office.microsoft.com/en-us/excel/HP052090471033.aspx

days360

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.

• $start_date the start date.
• $end_date the end date.
• $method if false then US/NASD Method is used, otherwise the European Method is used.

• 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.

• http://office.microsoft.com/en-us/excel/HP052090471033.aspx

hour

declare function excel-datetime:hour (
            $time as xs:time 
 ) as xs:integer?

Returns the hour of a time value.

• $time the time.

• 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.).

• http://office.microsoft.com/en-us/excel/HP052091151033.aspx

minute

declare function excel-datetime:minute (
            $time as xs:time 
 ) as xs:integer?

Returns the minutes of a time value.

• $time the time.

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

• http://office.microsoft.com/en-us/excel/HP052091781033.aspx

month

declare function excel-datetime:month (
            $date as xs:date 
 ) as xs:integer?

Returns the month of a $date.

• $date the date.

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

• http://office.microsoft.com/en-us/excel/HP052091841033.aspx

networkdays

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.

• $start_date the start date.
• $end_date the end date.

• 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.

• http://office.microsoft.com/en-us/excel/HP052091901033.aspx

networkdays

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.

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

• 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.

• http://office.microsoft.com/en-us/excel/HP052091901033.aspx

now

declare function excel-datetime:now (
 ) as xs:dateTime?

Returns the current date and time.

• The current date and time.

• http://www.w3.org/TR/xquery-operators/#func-current-dateTime

second

declare function excel-datetime:second (
            $time as xs:time 
 ) as xs:decimal?

Returns the seconds of a $time value.

• $time the time.

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

• http://office.microsoft.com/en-us/excel/HP052092511033.aspx

time

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.

• $hour the hour.
• $minute the minute.
• $second the second.

• A time given the hours, minutes and seconds.

• http://office.microsoft.com/en-us/excel/HP052093151033.aspx

today

declare function excel-datetime:today (
 ) as xs:date?

Returns the current date.

• The current date.

• http://www.w3.org/TR/xquery-operators/#func-current-date

weekday

declare function excel-datetime:weekday (
            $date as xs:date 
 ) as xs:integer?

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

• $date the date.

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

• http://office.microsoft.com/en-us/excel/HP052093151033.aspx

weekday

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.

• $date the date.
• $return_type 1 for Numbers 1 (Sunday) through 7 (Saturday). 2 for Numbers 1 (Monday) through 7 (Sunday). 3 for Numbers 0 (Monday) through 6 (Sunday).

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

• excel-err:Value if $return_type is outside the range [1,3].

• http://office.microsoft.com/en-us/excel/HP052093361033.aspx

year

declare function excel-datetime:year (
            $date as xs:date 
 ) as xs:integer?

Returns the year corresponding to a date.

• $date the date.

• The year corresponding to a date.

• http://office.microsoft.com/en-us/excel/HP052093431033.aspx