FORMULA MS-EXCEL PART-4

                                DATE & TIME  FUNCTION 

1. TODAY ( ) :-

Summary 

The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current date and time, use the NOW function.

Purpose 

Get the current date

Return value 

Valid Excel date

Syntax 

=TODAY ()

Arguments 

Usage notes 

The TODAY function takes no arguments, and returns the current date, updated whenever a worksheet is changed or opened. You can also use F9 to force the worksheet to recalculate and update the value.

• If you need a static date that won't change, you can enter the current date using the keyboard shortcut Ctrl + Shift + ;
• If you need current date and time, use the NOW function.

Examples

=TODAY() // current date
=TODAY()-7 // one week ago
=TODAY()+7 // one week later

2. NOW ( ) :-

Summary 

The Excel NOW function returns the current date and time, updated continuously when a worksheet is changed or opened. The NOW function takes no arguments. You can format the value returned by NOW as a date, or as a date with time by applying a number format.

Purpose 

Get the current date and time

Return value 

A serial number representing a particular date and time in Excel.

Syntax 

=NOW ()

Arguments 

Usage notes 

NOW takes no parameters but requires empty parentheses. The value returned by NOW will continually update each time the worksheet is refreshed (for example, each time a value is entered or changed). Use F9 to force the worksheet to recalculate and update the value.

If you need a static time that won't change,  you can enter the current time using the keyboard shortcut Ctrl + Shift + :

3. DAY  ( ) :-

Summary 

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function.

Purpose 

Get the day as a number (1-31) from a date

Return value 

A number (1-31) representing the day component in a date.

Syntax 

=DAY (date)

Arguments 

• date - A valid Excel date.

Usage notes 

The DAY function returns the day value in a given date as a number between 1 to 31 from a given date. For example, with the date Janunary 15, 2019 in cell A1:

=DAY(A1) // returns 15

You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function. For example, to change the year of a date in cell A1 to 2020, but leave the month and day as-is, you can use a formula like this:

=DATE(2020,MONTH(A1),DAY(A1))

See below for more examples of formulas that use the DAY function.

Note: in Excel's date system, dates are serial numbers. January 1, 1900 is number 1 and later dates are larger numbers. To display date values in a human-readable date format, apply a the number format of your choice.

Notes

• The date argument must be a valid Excel date. 

4. MONTH ( ) :-

Summary 

The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function.

Purpose 

Get month as a number (1-12) from a date

Return value 

A number between 1 and 12.

Syntax 

=MONTH (date)

Arguments 

• date - A valid Excel date.

Usage notes 

The MONTH function extracts the month from a given date as number between 1 to 12. For example:

=MONTH("23-Aug-2012") // returns 8
=MONTH("11-May-2019") // returns 5

You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function. See below for more examples of formulas that use the MONTH function.

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply a the number format of your choice.

Notes

• Date must be a valid Excel date.
• MONTH will return a #VALUE error on text values.

5. YEAR ( ) :-

Summary 

The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.

Purpose 

Get the year from a date

Return value 

Year as 4-digit number

Syntax 

=YEAR (date)

Arguments 

• date - A valid Excel date.

Usage notes 

The YEAR function extracts the year from a given date as a 4-digit number. For example:

=YEAR("23-Aug-2012") // returns 2012
=YEAR("11-May-2019") // returns 2019

You can use the YEAR function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function:

=DATE(YEAR(A1),1,1) // first of same year

See below for more examples of formulas that use the DATE function.

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply a the number format of your choice.

Notes

• Date must be a valid Excel date.
• YEAR will return a #VALUE error on text values.