Sun 14 Jul 2019

Tips & Tricks 171 - Calculate Next Working day if date is of Weekend / Holiday

By |Sunday, July 14th, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 11-Mar-19 as the next workday. (more…)

Sun 03 Mar 2019

Tips & Tricks 169 - Calculate Previous Working day if date is of Weekend / Holiday

By |Sunday, March 03rd, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate Previous Working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 8-Mar-19 as the previous workday. (more…)

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 16 Jan 2016

Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

By |Saturday, January 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , |2 Comments

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 formulas. So, I will be talking about a total of 3 formulas.

2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.

(more…)

Sat 31 May 2014

Tips & Tricks 5 - Allow Entry of Dates if they are not Weekends

By |Saturday, May 31st, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

1. Select the Cell > Data Tab > Data Validation > Data Validation

2. Under Settings Tab, in Allow, select Custom

3. Put following formula

=WEEKDAY(A1,16)>2

1

4. Customize your Input and Error Message as per your requirements.

Sat 24 May 2014

Tips & Tricks 4 - Allow Entry of Date for a Particular Day only

By |Saturday, May 24th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. Select the Cell > Data Tab > Data Validation > Data Validation

2. Under Settings Tab, in Allow, select Custom

3. Let's assume that we want users to enter only those dates which are Wednesdays. Put following formula in

=WEEKDAY(A1)=4

1

4 is for Wednesdayday. 1 is for Sunday and 7 is for Saturday other values lying in between.

4. Customize your Input and Error Message as per your requirements.