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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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,275
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,275
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,453
Messages
5,523,072
Members
409,496
Latest member
SWH

This Week's Hot Topics

Top