I had already written about one formula where I dealt with coming day formula – Article 3 – Show Date for a Coming Day (e.g. Coming Saturday) I wanted to revisit this with following things in mind – 1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional…
Category: Articles
Article 33 – Rank when Duplicates Exist (Ties)
Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.
Article 32 – Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)
Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here – 26-Dec-15 and 27-Dec-15) and any holiday…
Article 31 – Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
You will encounter slab billings in two very common documents – One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them) 0 – 50 Units – $1.5…
Article 30 – VBA – Approaches for Unique Count and Time Performance Results for the Same
As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time. https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx We will see performance of these approaches with following…
Article 29 – Traffic Lights Conditional Formatting for Project Status for Text R / A / G
We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text. We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light…
Article 28 – How to Unhide all Tabs (Worksheets)
If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same. Option 1 – Use Custom Views 1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the…
Article 27 – Remove Leading Apostrophe
Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more…
Article 26 – Converting Amount into Words (Indian Currency – Rupees and Paise)
Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh…..unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System) Below are numerical equivalent of Indian words Lakh – 1,00,000 Crore – 1,00,00,000…
Article 25 – Reverse FIND / SEARCH & MID Function
Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet. Hence, the only option before us is to build them through formulas. Let's build a reverse FIND…