Date and time functions (I)

NOW() Function

This function returns the sysem's current date and time in date and time format.

Example: =NOW() returns 09/09/2004 11:50

 

YEAR Function(serial_number)

This function has as it parametre a numerical value and returns the year in year format in the range 1900-9999.

Example: =YEAR(38300) returns 2004. We could pass the reference to a cell that contains a date instead of a numerical value:=YEAR(B12) also returns 2004, if in cell B12 I have the value 01/01/2004.

 

DAY Function(serial_number)

Returns the day of the month that corresponds to the numerical value given.

Example: =DAY(38300) returns 9.

 

360DAYS function(start_date;end_date;method)

It calculates the number of days between two given dates on a 360 days per year basis.The initial and final date paremetres should be introduced by means of the function Date(year;month,day). The parametre for method is logical (true, false), V --> European method, F or omitted--> American method.*

European Method: The initial or final dates that correspond to the 31st of the month become the 30th of the same month.

American method: If the initial date is the 31st of the month, it becomes the 30th of the same month. If the last date is the 31st of the month and the initial date is before the 30th, the final date becomes the 1st of the following month; otherwise the final date becomes the 30th of the same month.

 

Example: =DAYS360(date(1975;05;04);Date2004;05;04)) returns 10440.

 

WEEKDAY Function(serial_number;return_type)

It returns a figure between 1 and 7 that indicates the day of the week, with the type parametre you can specify from which day the week starts. In the American style we will enter type=1 (Sunday=1 and Saturday=7), for Eurpean style we will enter type=2 (Monday=1 and Sunday=7).

Example: =WEEKDAY(38300;2) returns 2.

 

DATE Function(year;month;day)

Returns the date on date format. This function is particularly useful to indicate the full date in cells where we have data of the day, month and year separate.*

Example: =DATE(2004;2;15) returns 15/02/2004.

 

DATEVALUE Function(date_text)

It returns the date in date format converting it in text format as parametre. The date as parametre must have the style "day-month-year". *

Example: =DATEVALUE("12-5-1998") returns 12/05/1998

Continues in the next page...



 

 

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.