Sometimes, you might face a problem that you input a number say 567 and it automatically becomes 5.67. If you enter 8, it becomes 0.08. To correct this problem > File > Options > Advanced and uncheck Red Zone
Category: Tips and Tricks
Tips & Tricks 145 – Determine the First Sunday or any other Day given Weeknumber
If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula =CEILING(("1JAN"&A1)-14,7)+8+7*(5-1) Where A1 has the year say A1=2016 5 is the Week Number which you can replace. For Finding Monday, add 1 in the formula, add 2 for Tuesday and…
Tips & Tricks 144 – Enter the Last Save Date and Time
1. Open your workbook and ALT+F11 2. Locate your Workbook name in Project Explorer Window 3. Right click on your workbook name > Insert > Module 4. Copy paste the Macro code given 5. Save your file as .xlsm To get Last Save Date, enter following in a cell =LastSaveDate() To get Last Save Time,…
Tips and Tricks 143 – Increment a Number when Workbook is Opened (Invoice or PO Number)
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double Click on "This Workbook" 5. Copy paste the Macro code given – Replace Sheet1 and B1 as per your need 6. Save your file as .xlsm if you intend to reuse…
Tips and Tricks 142 – Determine Number of Working Days in a Year
Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be – =NETWORKDAYS("1JAN"&A1,"31DEC"&A1) The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel…
Tips & Tricks 141 – How to Disable Quick Analysis Tool in Excel 2013
In Excel 2013, we have a new feature called Quick Analysis Tool. If you select a range and press CTRL, you are presented with Quick Analysis Tool. It offers various commonly used functions which you can perform. Few people don't like this. To disable it permanently – File > Options > Uncheck the Red Zone…
Tips & Tricks 140 – Multiple Hyperlinks within Excel Text Box
You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc…Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink. It is possible to circumvent this behavior by work-around. You can…
Tips & Tricks 139 – Convert from Julian Dates to Excel (Gregorian) Dates
In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse. For 7 Digits Julian Dates, following formula should be used =DATE(LEFT(A1,4),1,RIGHT(A1,3)) For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall…
Tips & Tricks 138 – Convert from Excel Date (Gregorian Date) to Julian Date
Q. First what is a Julian Date? A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems. 7 Digits – YYYYDDD – 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year) 5 Digits – YYDDD –…
Tips & Tricks 137 – VBA – Delete Blank Rows for a Range Quickly (without Looping)
Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks. You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same On Error Resume Next Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0