Find offday using formula

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.


Start Date End Date No of "offday" Date Calender
05-Aug 13-Aug 2 5-Aug-10 Working
6-Aug-10 Working
7-Aug-10 Working
8-Aug-10 offday
9-Aug-10 Working
10-Aug-10 Working
11-Aug-10 offday
12-Aug-10 Working
13-Aug-10 Working

Many thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.

Start date End date No of offdays Date Calender
5 Aug 13 Aug 2 5 aug working
6 Aug working
7 aug working
8 aug offday
9 aug working
10 aug working
11 aug offday
12 aug working
13 aug working
14 aug working

Resent due to earlier example not so clear
 

Mr_Roscoe

Board Regular
Joined
Mar 28, 2006
Messages
200
Thiyagu,

I suspect what you require is a COUNTIF formula in cell C2 as follows:

C2=COUNTIF(E2:E11,"offday")

E2:E11 is the list with working or offday - adjust this range if required.

cheers, Ian R.
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Resent again cause still not so clear

Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.

Start date----------End date------ No of offdays-----
5 Aug--------------13 Aug ----------2------------

Date-----Calender
5 aug----- working
6 Aug -----working
7 aug------working
8 aug ------offday
9 aug-------working
10 aug------working
11 aug------offday
12 aug------working
13 aug------working
14 aug------working
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131

ADVERTISEMENT

Hi there ,

thanks a lot for ur help ...the formula at C2 should count no of offdays based on "start days" to "End days"..
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,449
Excel Workbook
ABC
1Start DateEnd DateNo of "offday"
25-Aug13-Aug2
36-Aug-10Working
47-Aug-10Working
58-Aug-10offday
69-Aug-10Working
710-Aug-10Working
811-Aug-10offday
912-Aug-10Working
1013-Aug-10Working
...
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))
 
Last edited:

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131

ADVERTISEMENT

I thinks this solution should work ,,i not try yet..
really thanks a lot for this solution,,

again thanks a lot...
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
i had try ..it's work ...Thanks a lot for AlphaFrog & Mr_Roscoe..

Thanks a lot....many thanks
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,449
If you have Excel 2007 or later, you could use the COUNTIFS function instead of the SUMPRODUCT function

=COUNTIFS(A3:A20, ">="&A2, A3:A20, "<="&B2, B3:B20, "offday")
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
I use excel 2003 , it's work great ..again thanks a lot for ur help
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,988
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top