Suppose you are given a date and you are asked to calculate Previous Working day if date is of weekend. If date is a regular workday, then you should show the same date. For example – 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or…
Category: Tips and Tricks
Tips & Tricks 168 – Sum Cells for a Particular Color
This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code.. 1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name >…
Tips & Tricks 169 – Get the Source of a Pivot Table
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module 5. Copy & Paste the below code in this module Now, you can call this function like =GetPivotRange(E5) Where E5 is any…
Tips & Tricks 167 – Shortcut for Refresh All Pivot Tables Not Working
To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task. But on most of the computer this shortcut may not work. The reason is most…
Tips & Tricks 166 – Convert a Number to Weekday Name
Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday =TEXT(A1&"Jan2017","dddd") To show only 3 characters of the Weekday Name =TEXT(A1&"Jan2017","ddd") You can add a number to A1 if you want to show some other Weekday Name Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just…
Tips & Tricks 165 – Convert Weekday Names to Numbers
Suppose Cell A2 contains weekday names like Sunday, Monday…..(or Sun, Mon…), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7. =ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0) =MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0) If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1…
Tips & Tricks 163 – Convert Alphabets to Numbers
If you want to convert a, b, c….z to 1, 2,3….26, the you can use following type of formula – =CODE(UPPER(A1))-64
Tips & Tricks 162 – Convert a Month Name to Month Number
Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number =MONTH(1&A1) =–TEXT(1&A1,"m") In case, cell A1 contains the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).
Tips & Tricks 161 – When is Thanksgiving Day in a Year
Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November. Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.