Excel Tricks- Formula and Function

What’s the Current Date or Time?

You may be familiar with Excel’s NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you’ve set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.
Spot Duplicate Entries
  • Select the range (in this case, A1:B18).
  • Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
  • Select Formula Is from the first drop-down list box, and enter=COUNTIF($A$1:$B$18,A1)>1 in the second box.
  • Click the Format button to bring up the Format Cells dialog box.
  • Select the Patterns tab, and specify a background color.
  • Click OK twice to return to your worksheet.
  • If the range contains any duplicate entries, they will be flagged with the background color you chose in Step 5.

Generate Random Numbers

Need to create random numbers?  You can do it in Excel. 
To generate a number between 0 and 1, type =RAND() in a cell.
To generate a number between 1 and 100, type =RAND()*100.

Vlookup Formula – Organizing the Data Table

Rules for organizing the data table for proper use of the Vlookup formula:
The Vlookup formula searches for the lookup criteria in the leftmost column of the data table. It is recommended that the whole sheet be used as the data table, so that Vlookup will automatically look at column A as the leftmost column.
Defining a Name for the sheet (to use it as the Table_array (the second argument in the Vlookup formula) will eliminate the need of updating the range reference in any Vlookup formula.

Calculating Number of Days, Weeks, Months and Years between Dates

To calculate the difference in days, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”d”)
To calculate the difference in weeks, use the INT function as shown in the following formula:
=INT((B2-A2)/7)
To calculate the difference in months, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”m”)
To calculate the difference in years, use one of the following two solutions:
Use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”y”)
OR
Use the YEAR, MONTH, AND, and DAY functions as shown in the following formula:
=YEAR(B2)-YEAR(A2)-(MONTH(B2)<day(a2)))
To calculate the number of months over years, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”ym”)
To calculate the number of days over years, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”yd”)
<day(a2)))
Advertisements

Posted on April 21, 2016, in Uncategorized and tagged , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: