Date Functions

In Addition to Dynamic date handling Excel provides a wide range of Date related functions to enable us to manipulate dates as most extend.

• Date Function: This function used to compose date

Syntex: Date(year,month,day)
Example:
=date(2009,12,17)
composes: 17th December'2009

• you can also use DateValue function to convert String date into date

Syntex: DateValue("date")
Example:
=DateValue("2009/12/17")
Retunrs: 40164  (that is equals to 17th December'2009)

this value can be used further in any function as parameter.

Best part of this function is it flexibility that allows to enter the date in any format, As we take above example we could enter date "17/12/2009", "17 dec 09", "17-December-2009"

• Day:  This function returns the day from any date provided

Syntex: Day(date) or Day("mm/dd/yyyy")
Example: Day("12/01/2009")
Returns: 01

Note: Excel uses the System's date format by default it is 'M/d/yyyy' you can change this format using control panel - Regional Settings - Dates



Month: Like day function month returns the month from any date provided
Syntex: Month(date) or Month("mm/dd/yyyy")
Example: Month("12/01/2009")
Returns: 12

Year: Like day & Month functions year returns the year from any date provided

Syntex: Year(date) or Year("mm/dd/yyyy")
Example: Year("12/01/2009")
Returns: 2009

Weekday: Weekday function returns the Weekday that is falling on the date provided

Syntex: =Weekday(date)
Example: =Weekday("2009/12/17")
Returns 5 i.e. Thursday
begning from Sunday = 1, Monday = 2 and so on Saturday = 7

Today(): This  function returns the current date. And as the date is changed Excel automatically updates today function on next calculation to the prevailling date this way it will always returen the present date.

Value returned by this function is variable it changes as the date changes to insert constant current date  that doesn't change with change date use Keyboard Shortcut Ctrl + ;

Date.Diff(): this function calculates the differances in months & year between two dates.
Syntex: date.diff(date1,date2)
Example: =DATE.DIFF("10/10/2008",today())
Return: 1 year 2 months  (Assuming today is 17-December-2009)
This function does not calculates differances in days.

For Additional Details about Dates Visit: http://xlxpart.blogspot.com/2009/12/dates-in-excel.html

Excel Sumif

At several times we require calculating the total of our data tables based on some criteria.


Lets take an example:

Suppose we have a excel sheet that has the data of bills submitted by us and we calculating the aging of our bills from the date of submission. Only the bill submission date is older than 30 days then we forward the total start follow up by commercial department.

Here in above sheet we have the Bill submission date we subtract the submission date from today’s date in column E (Instead of entering today’s date in saperate column we can also subtract it directly from Today() function.

We put the Function

=Sumif(F2:F8,”>30”,C2:C8)

This function has three parts

=SUMIF(range,criteria,sum_range)



1. Range: first specify the range that we have to check if it matches to Criteria

2. Criteria: in this we provide the logical equation for the range we want to check

“=30” : Equals to 30

“>=30” : Grater then or equals to 30

“<=30” : Less then or equals to 30

“<30” : Less then 30

“<>30” : Not Equals to 30

>,< and = are the logical operator that can compare two values and can gives True or False results.

3. Sum_Range: in this part we specify range of which we have to take total.

If we ommit the last optional argument (sum_range) the SUMIF would sum all cells in the range F1:F8 which are greater than 30.

Some Excel Shortcut Keys

Shortcut Keys Description
F2 Edit the selected cell.
F5 Goto a specific cell. For example, C6.
F7 Spell check selected text and/or document.
F11 Create chart.
Ctrl + Shift + ; Enter the current time.
Ctrl + ; Enter the current date.
Alt + Shift + F1 Insert New Worksheet.
Shift + F3 Open the Excel formula window.
Shift + F5 Bring up search box.
Ctrl + A Select all contents of the worksheet.
Ctrl + B Bold highlighted selection.
Ctrl + I Italic highlighted selection.
Ctrl + K Insert link.
Ctrl + U Underline highlighted selection.
Ctrl + 5 Strikethrough highlighted selection.
Ctrl + P Bring up the print dialog box to begin printing.
Ctrl + Z Undo last action.
Ctrl + F9 Minimize current window.
Ctrl + F10 Maximize currently selected window.
Ctrl + F6 Switch between open workbooks / windows.
Ctrl + Page up Move between Excel work sheets in the same Excel document.
Ctrl + Page down Move between Excel work sheets in the same Excel document.
Ctrl + Tab Move between Two or more open Excel files.
Alt + = Create a formula to sum all of the above cells
Ctrl + ' Insert the value of the above cell into cell currently selected.
Ctrl + Shift + ! Format number in comma format.
Ctrl + Shift + $ Format number in currency format.
Ctrl + Shift + # Format number in date format.
Ctrl + Shift + % Format number in percentage format.
Ctrl + Shift + ^ Format number in scientific format.
Ctrl + Shift + @ Format number in time format.
Ctrl + Arrow key Move to next section of text.
Ctrl + Space Select entire column.
Shift + Space Select entire row.