This author has not yet filled in any details.
So far eforexcel has created 452 blog entries.
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 02 Jun 2019

## Excel Quiz 57

By |Sunday, June 02nd, 2019|Categories: Quizzes|Tags: , , , |0 Comments

## Excel Quiz 57

A general quiz on Excel

Wed 15 May 2019

## Downloads 20 - Excel based Consolidated Minutes of Meeting (MOM) Template

While earlier MOM template was or individual meetings, this MOM template can be used for all meetings held. Advantage is that you have running records of attendance, discussion and action items. (more…)

Mon 15 Apr 2019

## Tips & Tricks 170 - Convert your formulas to Absolute References

By |Monday, April 15th, 2019|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations. (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||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 23 Feb 2019

## Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy and Paste the below code in the module
6. Save your file as .xlsm

=SumColorCells(A1:C6,255,255,0)

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

---- Macro by E for Excel ----

```Function SumColorCells(Rng As Range, R, G, B)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
SumColorCells = SumColorCells + Cell
End If
Next Cell
End Function```

Wed 16 Jan 2019

## Solution - Challenge 68 - Need a Data Validation Formula

By |Wednesday, January 16th, 2019|Categories: Solutions|Tags: |0 Comments

Below is a proposed solution to the Challenge 68 - Need a Data Validation Formula

=IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(--RIGHT(A2,4)=2018,AND(--LEFT(A2,2)>=1,
--LEFT(A2,2)<=12),IF(AND(--RIGHT(A2,4)<=2021,
--RIGHT(A2,4)>=2019),AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=4)))),FALSE)

Sun 16 Dec 2018

## Challenge 68 - Need a Data Validation Formula

By |Sunday, December 16th, 2018|Categories: Challenges|Tags: |1 Comment

Need a data validation formula for following assuming you are putting data validation in cell A2.

The user should be able to enter a date in following format

1. MM/YYYY for the year 2018
2. QQ/YYYY for the years 2019 to 2021

The solution to above challenge will be published after a month i.e. on 16-Jan-18.

Sat 17 Nov 2018