Monday, August 29, 2011

Turn Off AutoRecover For Individual Workbooks

In Excel 2007 and 2010, there is a feature called AutoRecover that saves your open workbooks at regular intervals. In the event of a power failure or computer crash or freeze up, this can help you recover your unsaved work. This feature is turned on by default and is hardly noticeable as it happens in the background.

However, you may notice AutoRecover saving files, and may even get annoyed by it, if you have one or more large workbooks open, as it could tie you up for 5 to 10 or more seconds. I often have very large workbooks open that contain only reference information (i.e. files that I'm not making changes to) and other workbooks which I don't require to be saved by the AutoRecover feature.

To avoid the unnecessary saving of these files, there is an option available to turn off AutoRecover for individual workbooks.




Here's how...

1) Click the File tab (2010) or the Office Button (2007);

2) Click Options (2010) or Excel Options (2007);

3) Click the Save option on the left;

4) In the 'AutoRecover exceptions for' section, check the 'Disable AutoRecover for this workbook only' option.

(adapted from "excel addict")

Friday, August 19, 2011

"DATEDIF" function to calculate the number of days/months/years between dates

A simple subtraction between two date values gives you the number of days between the two days. However, to actually calculate the exact number of days or number of completed months or number of completed years, Excel offers a hidden function called "DATEDIF".

Syntax: =datedif(date 1, date 2,criteria), where
date 1 is the start date or the older of the two dates
date 2 is the end date or the recent date
Criteria can be as follows:
  "d" - number of completed days between the two dates
  "m" - number of completed months between the two dates
  "y" - number of completed years between the two dates
  "yd" - number of completed days between dates ignoring the year i.e. considering them to be in the same year
 "ym" - number of completed months between dates ignoring the year i.e. considering them to be in the same year
 "md" - number of completed days between dates ignoring the year & month i.e. considering them to be in the same year and same month

Therefore if I want to calculate my age, I can write the formula as:

Date 1: 1-Jan-1980 (H4 cell)
Date 2: 19-Aug-2011 or today (I4 cell)
Formula: =DATEDIF(H4,I4,"y")&" years "&DATEDIF(H4,I4,"ym")&" months and "&DATEDIF(H4,I4,"md")&" days"
Result: 31 years 7 months and 18 days


Caution:
  • Date 1 ought to be lesser than or older than Date 2
  • of course, both dates should be valid date formats
Try it... its interesting!!

Thanks Charan for reminding me on this function, which I had stopped using.