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 23 Apr 2016

Downloads 02 - Template 02 - A Highly Customizable Perpetual Yearly Calendar Template in Excel

By |Saturday, April 23rd, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , |0 Comments

Download link for this Calendar Template 02 - Calendar Template Yearly

The second template in the series is a calendar. Just download it once and use every year. Following are the features -

1. The calendar is perpetual in nature. It supports all the years from 1900 to 9999. You just need to change the year every year or you can leave the year field blank. In that case, it would take the current year as the year.
2. It features a list where you can put important dates and those dates will automatically be shaded.
3. You can define your weekends. It can be one weekend or two weekends.
4. You can define your start day of the week. It can be any day between Mon to Sun.
(more…)

Sat 19 Jul 2014

Tips & Tricks 33 - Generate Week WORK Day Names Mon to Fri or Monday to Friday

By |Saturday, July 19th, 2014|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

1. Put "Mon" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Monday" without quotes.
Note - The start day can be any WORK day. "Mon" is chosen for illustration purpose only. The start day will be whatever is put in cell A1.

2. Drag the bottom left corner till row 5 (or to any number of rows) by pressing right click.

3. Leave Right Click.

4. From the menu which pops up, select Fill Weekdays. It will populate all cells with workdays of the week except Sat and Sun i.e. with only Workdays.

1