|
![]() |
![]() |
CONVDATE or @CONVDATE Converts a number in the format YYMMDD to a date serial number. CONVDATE(date) date a date given in YYMMDD format Mesa stores dates in terms of date serial numbers that can be used in various date functions, and then translated back into the desired format. Dates after the year 2000 can be specified by placing a 1 before the year. =CONVDATE(930210) -> 8441 : This is the date-number of 10-Feb-93 =CONVDATE(1100410) -> 14709 : This returns the date number of 10-Apr-2010. DATE or @DATE Returns a date serial number for the year, month and day. DATE(yr, mon, day) yr the year since 1900 mon the month day the day of the month A date serial number is a number used by Mesa to represent dates so that they may be formatted in a number of different ways. The number corresponds to the number of days since January 1, 1970. Dates after the year 2000 can be specified by placing a 1 before the year. =DATE(92,8,24) -> 8271 : This is the date number for 24-Aug-92. =DATE(79,10,31) -> 3590 : This is the date-number for 31-Oct-79. DATEVALUE, DAYS or @DATEVALUE Datevalue converts a date string to a date serial number. If the value is a number it just returns the number. DATEVALUE(dtstr), DATEVALUE(num) dtstr a string corresponding to a date num any number This function converts dates entered as strings into the date serial number used by Mesa's date functions. This function will not effect numeric input. =DATEVALUE("02/04/92") = 8069 A3: 'Jul-94 =DATEVALUE(A3) = 8947 : This is the date number for 1-Jul-94 DAY or @DAY Returns the day of the month for a date serial number. DAY(date) date a date serial number DAY returns the day of the month for a date serial number date. The date can also be a reference to a cell containing a date. @day(9131) -> 4 A3: 18-Jul-92 =DAY(A3) -> 18 =DAY(NOW) : This formula will return the current day of the month. HOUR or @HOUR Returns the hour for a date serial number. HOUR(date) date a date serial number HOUR returns the hour for a date serial number date. The date can also be a reference to a cell containing a date. If a time is specified in a cell, but no date is specified, the integer portion of the date serial number is assumed to be zero. =HOUR(TIMEVALUE("23:00:00")) -> 23 =HOUR(NOW) : This formula will return the current hour of day. MINUTE or @MINUTE Returns the minute for a date serial number. MINUTE(date) date a date serial number MINUTE returns the minutes from a date serial number date. The date can also be a reference to a cell containing a date. If a time is specified in a cell, but no date is specified, the integer portion of the date serial number is assumed to be zero. =MINUTE(TIMEVALUE("11:35:20")) -> 35 =MINUTE(NOW) : This formula will return the current number of minutes past the hour. MONTH or @MONTH Returns the month for a date serial number. MONTH(date) date a date serial number MONTH returns the month of the year for a date serial number date. The date can also be a reference to a cell containing a date. =MONTH(DATEVALUE("12/24/92")) -> 12 =CHOOSE(MONTH(NOW)-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") : This formula will display the current month. NETWORKDAYS or @NETWORKDAYS Returns the number of working days between the given dates. NETWORKDAYS(date1, date2[,holidays]) date1 the starting date serial number date2 the ending date serial number holidays the serial numbers of any intermediate holidays This function allows you to determine the number of working days between the two dates date1 and date2. You can optionally list any holidays that fall between the two dates, which will not be counted as working days. =NETWORKDAYS(NOW,NOW+10) : This returns the number of working days between now and 10 days from now. NEXTMONTH or @NEXTMONTH Returns a date serial number one calendar month after date. NEXTMONTH(date) date a date serial number This function returns the date serial number of the date one month from the given date. If the day of the month is greater than 28, then it is set to 28. A3: 27-Jul-92 =NEXTMONTH(A3) -> 8274 : This is the date-number for 27-Aug-92. =NEXTMONTH(11381) -> 11409 : This is the date-number for 28-Mar-2001. NEXTYEAR or @NEXTYEAR Returns the next year of the date. NEXTYEAR(date) date a date serial number This function returns the date serial number of the date one year from the given date. If the date is 2/29 of a leap year, the date returned is 2/28 of the following year. =NEXTYEAR(NOW) : This formula returns a date-number one year from the current date. =NEXTYEAR(DATE(93,6,20)) -> 8936 : This is the date-number for 20-Jun-94. SECOND or @SECOND Returns the seconds of a date serial number. SECOND(date) date a date serial number SECOND returns the seconds from a date serial number date. The date can also be a reference to a cell containing a date. If a time is specified in a cell, but no date is specified, the integer portion of the date serial number is assumed to be zero. =SECOND(TIMEVALUE("10:31:19")) -> 19 =SECOND(0.26903) -> 24 : This formula returns the seconds for the date-value for 06:27:24 AM. TIME or @TIME Returns a date serial number for a given time. TIME(hr, min, sec) hr the hour, from 0 to 23 min the minutes sec the seconds This function takes the time, for a given 24 hour time into the corresponding date serial number used by Mesa's date functions. =TIME(11,30,00) -> 0.479167 : This is the date-number for 11:30 AM. =NOW+TIME(8,0,0) : This formula returns a date-number for the time 8 hours from now. TIMEVALUE or @TIMEVALUE Converts a string to a date serial number. TIMEVALUE(time-str) time-str a string corresponding to a time This function converts time entered as string into the date serial number used by Mesa's date functions. A1: '10 A2: '58 A3: '29 =TIMEVALUE(A1&":"&A2&":"&A3) = 0.45728 : This formula takes the string contents of cells A1, A2 & A3 and concatenates them into the string "10:58:29", then converts that string to a time value. A3: 12:34 am =TIMEVALUE(A3) -> 0.023611 : This is the date-number for 12:34 am. WEEKDAY or @WEEKDAY Returns the day of the week for a date serial number, 0 - Sunday through 6 - Saturday. WEEKDAY(date) date a date serial number This function is useful in determining what day of the week a given date serial number is. =WEEKDAY(NOW-5) : This returns the day of the week 5 days prior to today. A7: "6/20/82" =WEEKDAY(DATEVALUE(A7)) -> 0 : Returns the day of the week for 20-Jun-82. WORKDAY or @WORKDAY Returns the date serial number a given number of working days from the date serial number. Working days are all days other than Saturday, Sunday, and the optional holidays listed. WORKDAY(date, days [,holidays]) date a date serial number days any number of days holidays the serial number of any intermediate holidays This function takes a date serial number and determines the date serial number of the day a given number of working days from then. If there are any holidays listed, they will not be counted as working days by this function. You may list any combination of dates, cell addresses containing dates, and ranges containing dates as arguments for holidays. =WORKDAY(NOW,10) : This returns the date serial number 10 working days from now. YEAR or @YEAR Returns the year for a date serial number. YEAR(date) date a date serial number YEAR returns the year for a date serial number. The date can also be a reference to a cell containing a date. =YEAR(NOW-1000) : This returns the year of the date 1,000 days prior to the current date. A7: '6/20/82 =YEAR(DATEVALUE(A7)) -> 82 : This formula extract the year from the date-string "6/20/82". |
||
|
||