I am new to blogging but just thought of starting this activity, with one aim in my mind - to share the knowledge I have gathered till now in the field of excel functions and programming. My work involves me and my team to manage and manipulate data - which I have realized over the last few years if done manually, is troublesome, painstaking, erroneous and takes up a lot of time. I always encourage myself and my team to work on automated excel files to get work done - this not just speeds up your performance and efficiency, it also reduces errors that can creep in when you are doing your work in excel manually, because to err is human.
I will start sharing examples like the ones mentioned below and would request you to post any questions you might have - I will try to best answer them - this will help everyone to increase their knowledge about excel.
Today's example is a very small exercise that almost every manager / team leader comes across - regarding attendance management (also regarding showing day for a particular date). I was helping one of my team managers who faced this problem - he was maintaining an excel sheet to track attendance for a team and wanted to display the date and day for a particular date in different cells.
The first obvious solution that I gave to him was to link another cell to the main cell and have different formats to both cells, one showing the date and the other showing the day display format (Format Cells > Custom > [$-409]dddd ) but I wanted to do it via a formula, so here is what I came up with:
In the cell you want to display the day for a particular date, enter the following formula (assuming you are entering the date in cell A1):
=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
What this function does is, firstly it extracts what is the day number in chronological order for a week usign the "WEEKDAY" function, i.e. 1 for Sun, 2 for Mon and so on... then once this info is extracted, we use the CHOOSE function and use theWEEKDAY function result as - index_num, and enter Sun, Mon, Tue, etc. in the Val1, Val2, Val3, etc. boxes... so what's happening here is if today's date is entered, i.e. 1/11/2009, then WEEKDAY will return the value 1, and the CHOOSE will return Sun... so our formula will return "Sun" for 1/11/2009.
This is a very basic thing in excel but the idea here is that if you want to do something in excel, you can do it very easily with the help of inbuilt functions... just that you have to know about the functions and their features and carefully select appropriate functions so that by clubbing them, you can get your desired results.
Please let me know your thoughts... and keep visiting for more small but brilliant features in excel.
Thanks
Excel Champ
