vert line

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

 

Also go to:

Index help

Contents help