Sat 28 Feb 2015

Tips & Tricks 80 – Last Working Day of the Month If a Date is Given

By |Saturday, February 28th, 2015|Categories: Tips and Tricks|Tags: , |0 Comments

If A1 holds a date, the formula for calculating last Working Day of the month would be

=WORKDAY(EOMONTH(A1,0)+1,-1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula -

=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH(A1,0)+1,-1,D1:D10)

=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

Sat 21 Feb 2015

Tips & Tricks 79 – How to Know if a Year is a Leap Year

By |Saturday, February 21st, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula -

=MONTH(DATE(A1,2,29))=2

TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.

Sat 14 Feb 2015

Tips & Tricks 78 - Number of Days in a Month

By |Saturday, February 14th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose, you have been given a date say 15-Nov-14 and you have to determine how many days this particular month contains.

The formula which you need to use in the above case would be

=DAY(EOMONTH(A1,0))

Explanation - EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last date of the month.

Sat 14 Feb 2015

Challenge 9 - Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

By |Saturday, February 14th, 2015|Categories: Challenges|Tags: , |1 Comment

Given the below table, the challenge before you is to - Work out a formula for Customer spending Maximum amount and his Spend (Cells H4 and H5) for a given Month (H2) and starting alphabet (H3).

Cell H2 has the month in numbers 1 to 12 and H3 has starting alphabet.

If H2 is blank, then it should search irrespective of the month.
If H3 is blank, it should search irrespective of the starting alphabet.
Example - For the month of Mar and starting Alphabet M, Maddy has spent the highest amount and his spend is 345.

The relevant Excel file can be downloaded from here Customer Max Spend.

Note - If you wish, you may post your answer in Comments.

1

Sat 14 Feb 2015

Article 11 - How to Encrypt Excel Workbook

By |Saturday, February 14th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

Note - The article is for Excel 2010 / 2013 versions and doesn't cover any version prior to Excel 2010.

Excel 2010 / 2013 uses default 128 bit AES encryption which is considered highly secured. If you are interested in knowing all things about Excel security, I will recommend following MS article -

http://technet.microsoft.com/en-us/library/dn194021%28v=office.15%29.aspx

---- Update on 27-Jun-15 - Following is a very good article which contains some useful info on Excel security -

http://www.thespreadsheetguru.com/blog/2014/8/20/understanding-excels-password-security-methodology ----

Anyway, coming back to topic, there are 4 ways to encrypt Excel Workbook (Not counting VBA to do this which is not a safe way to encrypt).

1. Way 1 - Encrypt Document Button - This is recommended if you encrypt your workbook very often. (more…)

Sat 07 Feb 2015

Tips & Tricks 77 - Inserting picture in the comments box

By |Saturday, February 07th, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

The below discussion is for Excel 2013. If a cell is selected and it has comments, the comments will be displayed. Sometimes, there may be a need to display picture when you select a cell. I answered this question on Microsoft Community Forum and I have decided to reproduce it here.

1. Right click the cell > Edit Comment
2. Now, you need to right click on the border of the comments box. Do not select the comments inside the box and choose Format Comment. Since, you are inserting picture, hence you should not have comments in the box, anyway. So, there is no question of selecting comments inside the box. Once again, I will repeat that RIGHT CLICK ON COMMENTS BOX BORDER AND SELECT FORMAT COMMENTS.
3. Now, you will get Format Comments box and you need to select Color and Lines tab.
4. Click on Color: box and select Fill Effects.

1

5. When you click Fill Effects, choose Picture tab and select picture from there. By default, it starts connecting online to select picture. Most probably, you would like to select a picture on your computer. You may like to choose Work Offline here.

1

6. Now, you can have effect of picture in your comments like below -

1

Sat 07 Feb 2015

Excel Quiz 6

By |Saturday, February 07th, 2015|Categories: Quizzes|Tags: , , , |0 Comments

The sixth quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.