Friday, December 26, 2014

Find last date of current/given month using EOMONTH() function

We know that the Excel function EOMONTH(start_date, months) can be used for determining the last date of the month that is the number of months before or after the start_date.

But what if we want to find the last date of the current/given month. In this case, the months parameter should be 0. This will return (serial number) the last date of the same month as given in start_date.

How to avoid duplicate conditional formatting rules with copy/insert

When working on an Excel sheet which has Conditional Formatting rules, creating additional rows/columns/cells automatically create duplicates entries of Conditional Formatting. 

Below is the screenshot of first instance of Conditional Formatting entries

After copying a blank row to extend the table, Excel automatically creates duplicate entries as shown below in Conditional Formatting Rules Manager window

In order to avoid getting duplicate rules created, the workaround is to Insert a new row/column/cell and then copy over only the formulas to the new row/column/cell - this will retain originally created Conditional Formatting rules and will not unecessarity duplicate those rules.

This is applicable for Excel 2007/2010.