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

Sun 10 Sep 2017

## Challenge 65 - How Many Sundays on Last Date of Months

By |Sunday, September 10th, 2017|Categories: Challenges||2 Comments

This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below.

The answer to the solution will be published after a month i.e. on 10-Oct-17.

Sat 12 Aug 2017

## Downloads 16 - Sample CSV Files / Data Sets for Testing - Human Resources

Disclaimer - The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing.

Other data sets - Sales           Credit Card

You can download sample csv files ranging from 100 records to 500000 records. These csv files contain data in various formats like Text, Numbers, Date, Time, Percentages which should satisfy your need for testing.

This data set can be categorized under "Human Resources" category.

Below are the fields which appear as part of these csv files as first line.

Sat 17 Sep 2016

## Tips & Tricks 145 - Determine the First Sunday or any other Day given Weeknumber

By |Saturday, September 17th, 2016|Categories: Tips and Tricks||0 Comments

If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula

=CEILING(("1JAN"&A1)-14,7)+8+7*(5-1)

Where A1 has the year say A1=2016

5 is the Week Number which you can replace.

For Finding Monday, add 1 in the formula, add 2 for Tuesday and so on.

The above formula assumes that WEEKNUM function has Sunday as the starting day for the week. If you have any other day for the week as the starting day of the week, you will have to customize the above formula as per the need.

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

Sat 12 Jul 2014

## Tips & Tricks 32 - Generate Week Day Names Sun to Sat or Sunday to Saturday

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

1. Put "Sun" 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 "Sunday" without quotes.
Note - The start day can be any day. "Sun" 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 7 (or to any number of rows) by pressing left click. It will automatically generate the names of the week days.

Or

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

4. Leave Right Click.

5. From the menu which pops up, select Fill Days .