Date and Time spreadsheet functions
The date and time spreadsheet functions calculate dates and times, perform calculations involving business days, or convert location coordinates.
|
•
|
|
@ABDAYS Adds (or subtracts) a specified number of business days to a specified date.
|
|
•
|
|
@ACDAYS Adds a specified number of calendar days to a specified date.
|
|
•
|
|
@AMNTHS Adds a specified number of months to a specified date.
|
|
•
|
|
@BDAYS Returns the number of business days between two dates, inclusive of the second date.
|
|
•
|
|
@BUSDAY Returns a specified date if it is a business day, or the closest business day before (or after) the date.
|
|
•
|
|
@CDAYS Returns the number of calendar days between two dates, inclusive of the second date.
|
|
•
|
|
@D360 Returns the number of days between two dates, based on a 360-day year (twelve 30-day months).
|
|
•
|
|
@DATE Returns the date number for a specified year, month, and day; 0 = December 30, 1899.
|
|
•
|
|
@DATEDIF Calculates the number of years, months, or days between two dates.
|
|
•
|
|
@DATEINFO Returns information (for example, day of week or month of year) about a date number.
|
|
•
|
|
@DATEVALUE Returns the date number for a specified formatted date string.
|
|
•
|
|
@DAY Returns the day of the month (1-31) represented by a specified date/time serial number.
|
|
•
|
|
@DAYS360 Returns the number of days between two dates based on a 360-day year (twelve 30-day months).
|
|
•
|
|
@EMNTH Returns the date of the last day of the month in which a specified date falls.
|
|
•
|
|
@EOMONTH Returns the serial date number for the last day of the month a specified number of months before or after a start date.
|
|
•
|
|
@FBDAY Returns the date of the first business day of a month in which a specified date falls.
|
|
•
|
|
@HOLS Returns the number of holidays between two dates, excluding holidays that fall on weekends.
|
|
•
|
|
@HOUR Returns the number of hours past midnight (0-23) represented by a specified date/time serial number.
|
|
•
|
|
@ISBDAY Returns 1 if the specified date is a business day, or 0 if it is not.
|
|
•
|
|
@LBDAY Returns the date of the last business day of a month in which a specified date falls.
|
|
•
|
|
@LWKDAY Returns the date of the last specified weekday in a specified month.
|
|
•
|
|
@MDAYS Returns the number of calendar days in a specified month of a specified year.
|
|
•
|
|
@MINUTE Returns the number of minutes past the hour (0-59) represented by a specified date/time serial number.
|
|
•
|
|
@MNTHS Returns the number of whole months between two dates.
|
|
•
|
|
@MONTH Returns the month in number form (1-12) represented by a specified date/time serial number.
|
|
•
|
|
@NBDAY Returns the date of the first valid business day after a specified date.
|
|
•
|
|
@NENGO Converts a date to its kanji representation
|
|
•
|
|
@NETWORKDAYS Returns the number of days from a start date through an end date, excluding weekends and holidays.
|
|
•
|
|
@NOW Returns the date and time serial number for the current system date and time.
|
|
•
|
|
@NWKDAY Returns the date of the nth occurence of a specified weekday in a specified month.
|
|
•
|
|
@PBDAY Returns the date of the first valid business day before a specified date.
|
|
•
|
|
@SECOND Returns the number of seconds past the minute (0-59) represented by a specified date/time serial number.
|
|
•
|
|
@TIME Returns the time number for a specified hour, minute, and second. The hour is a numeric value between 0 and 23.
|
|
•
|
|
@TIMEVALUE Returns the time number for a specified formatted time string.
|
|
•
|
|
@TODAY Returns the date number for the current system date.
|
|
•
|
|
@WEEKDAY Returns a number (from 1 for Saturday to 7 for Friday) representing the day Date falls on
|
|
•
|
|
@WEEKNUM Calculates in which week of the year a specified date falls.
|
|
•
|
|
@WKDAY Returns a number (from 1 for Sunday to 7 for Saturday) representing the day Date falls on.
|
|
•
|
|
@WORKDAY Returns the serial number for a date that is a specified number of days before or after a specified date, optionally excluding weekends and/or holidays.
|
|
•
|
|
@YDAYS Returns the number of calendar days in a specified year.
|
|
•
|
|
@YDIV Returns the date of the beginning of the year division in which a specified date or specified number of divisions always falls.
|
|
•
|
|
@YEAR Returns the year number (-300 to 1299; 0 = 1900) represented by a specified date/time serial number.
|
|
•
|
|
@YEARFRAC Returns the year fraction representing the number of whole days between a specified starting and ending date.
|
Calculating dates and times
For calculation purposes, Quattro Pro stores all dates as serial integers beginning with 0 for December 30, 1899. The minimum, -109,571, equals January 1, 1600; the maximum, 474,816, equals December 31, 3199.
Quattro Pro stores times as decimal fractions; 0.000 represents 00:000:00, and 0.99999 represents 23:59:59. To format time expressions in your notebook, right-click the cell you want to format, click Selection properties, then click Numeric format.
Setting holidays for Date and Time functions
Business date functions have three arguments to specify which dates are holidays: Saturday, Sunday, and Holidays. By default, Saturday and Sunday are holidays. Setting the argument Saturday to 1 specifies that Saturday is a business day; setting Sunday to 1 specifies that Sunday is a business day. Use the argument Holidays to specify holidays that do not fall on weekends (unless weekends are used as business days). You can set Holidays to cells containing holiday dates, the date of a single holiday, or 0 to specify no special holidays.