DATEDIF

Have you ever needed to know the time (Months, Days, Years) between to dates? DATEDIF allows you to do this with one simple formula. It even has special rules in case you'd like to, for example, find the difference in days, but ignore years.

The syntax is as follows:

=DATEDIF (start_date, end_date, unit)

Where unit can be:

Unit	Result
"Y"	Difference in complete years
"M"	Difference in complete months
"D"	Difference in days
"MD"	Difference in days, ignoring months and years
"YM"	Difference in months, ignoring days and years
"YD"	Difference in days, ignoring years

Examples of situations where this might be useful:

• You are in HR and have a list of employee hire dates. You need to know how many months each employee has been employed (assume hire dates are in column A)
• =DATEDIF(A2,TODAY(),"M")
• You are reviewing contracts and need to know how many days it took from contract submission to contract approval (Assume submission dates are in column A and Approval dates in column B)
• =DATEDIF(A2,B2,"D")

I'll admit, I've used other Excel formulas to try and calculate this same result; this would have saved me time and headaches. Try it out!

(more info: https://exceljet.net/excel-functions/excel-datedif-function)

Paste Special - Multiplication

This isn't the fanciest trick, but it is one that can save you some time.

Let's say you dump some financial data from your accounting system, and expenses come out as negative numbers. But for your report, you need them to be positive numbers. Here's what you can do.

1) Type "-1" in any cell. Copy the cell.
2) Highlight the numbers you wish to change from negative to positive (or positive to negative)
3) Paste -> Paste Special
4) Click "Multiply," then OK

That's it! All values are multiplied by -1. You an also use this same process to divide, add, or subtract all numbers by a specific value.

INDIRECT

This formula takes a little time to set up, but once it's up and running it can save tons of time.
 Say you have multiple months for which you need to calculate data. Each months' data is on a separate sheet.












INDIRECT() allows you to reference a cell (containing the sheet name) which then points you to the correct sheet. Just set it up once and you can drag the formula without any manual formula changes. I used it nested in a COUNTIFS() statement, as follows:

=COUNTIFS(INDIRECT("'"&I$2&"'!"&"$K:$K"),">"&$H8,INDIRECT("'"&I$2&"'!"&"$K:$K"),"<"&$G10)

Learn more about it here: http://www.contextures.com/xlFunctions05.html

Ceiling

Ever need to round up to the multiple of a specific number?

Example: I need one full time employee for every 7 agents. But I can't hire 1/7th of an FTE. You could use min/max or IF statements, but =CEILING() is faster. Ceiling will round up to the next multiple of 7. So if I have 6 agents, there is 1 FTE, but if there are 8 agents, it is bumped to two.

Check it out here: https://exceljet.net/excel-functions/excel-ceiling-function

INDEX MATCH MATCH

INDEX MATCH MATCH

Say you want to pull a value from a table based on inputs cells. Both the row and column choices are dependent upon those inputs.

Example:

You have a list of food items in column A. Prices for those items are dependent on the month, January-December, and are listed in columns B-M. You want to be able to set up input cells were you could choose to lookup, say, the price of bananas in March. How can you do this?

INDEX MATCH MATCH

= INDEX ( entire matrix , MATCH ( vertical lookup value,  entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )

Learn more about it here:
http://www.mbaexcel.com/excel/how-to-use-index-match-match/

Not really an Excel tip...

This can be used in any Office program, to add a little zing to whatever you are creating. Have you ever wanted to create a number in a circle? Windings 2  is your answer:

http://speakingppt.com/2011/10/24/cheat-sheet-for-creating-numbers-inside-circles/

BTW, am I the only one who always says WingDings?

Search terms and phrases:
Number in Circle
Windings font

VBA for Various Copy/Paste Functions

I've been playing around with VBA a bit for a model I'm building. I needed a quick reference for various copy/paste functions, and found this website to be very helpful and easy to follow:

https://support.microsoft.com/en-us/kb/291308

Which is unusual, seeing how it's just the MS website. Still worth a look!

Search terms and phrases:
VBA
Copy Paste Range