Sat 18 Nov 2017

Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

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 add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Sat 04 Nov 2017

Tips & Tricks 165 - Convert Weekday Names to Numbers

By |Saturday, November 04th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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 and Sun = 7

=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)

Sat 07 Oct 2017

Tips & Tricks 164 - All Existing Charts have Disappeared

By |Saturday, October 07th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Accidental Pressing of CTRL+6 toggles hiding / unhiding of all objects which includes charts also. Pressing CTRL+6 will unhide all charts (objects) again.

Sometimes, Charts (objects) can be hidden through Selection Pane (Home tab>Find and Select>Selection Pane > This has Show All / Hide All buttons. In this case, you can show / hide appropriately.

Please note that both above are mutually exclusive. Hence, if use CTRL+6 to hide the objects, you can not use Show All of Selection Pane to show all objects and vice versa.

Sat 30 Sep 2017

Tips & Tricks 163 - Convert Alphabets to Numbers

By |Saturday, September 30th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

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

Sun 03 Sep 2017

Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

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).

Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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.

(more…)

Sat 03 Jun 2017

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Saturday, June 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

1

Sat 22 Apr 2017

Tips & Tricks 159 - Prompted to "Save File" even if I haven't changed anything

By |Saturday, April 22nd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed.

Point 1 - Microsoft has provided the answer to this question at following link -

(more…)

Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 04 Mar 2017

Tips & Tricks 157 - Search for Online Templates Greyed Out / Disabled

By |Saturday, March 04th, 2017|Categories: Tips and Tricks|Tags: , , , , |0 Comments

When we create a new workbook through File > New, we get a box where we can search for online templates. But, at times, it may be greyed out or disabled i.e. you can not type out anything out there.

1

To correct this, you need to enable internet setting for Excel.

File > Options (You can also invoke Excel options through shortcut ALT+T+O) > Trust Center > Trust Center Settings > Privacy Options > Allow Office to connect to Internet and Check this box.

1