# Find offday using formula

#### Thiyagu

##### Board Regular
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.

#### Thiyagu

##### Board Regular
Dear all ,

i would like find more solution,,pls see below example

<table x:str="" style="border-collapse: collapse; width: 569pt;" border="0" cellpadding="0" cellspacing="0" width="758"><col style="width: 62pt;" width="83"> <col style="width: 54pt;" width="72"> <col style="width: 86pt;" width="114"> <col style="width: 111pt;" width="148"> <col style="width: 91pt;" width="121"> <col style="width: 165pt;" width="220"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 62pt;" x:str="Start Date " height="18" width="83">Start Date </td> <td class="xl24" style="border-left: medium none; width: 54pt;" x:str="End Date " width="72">End Date </td> <td class="xl24" style="border-left: medium none; width: 86pt;" width="114">No of "Offday"</td> <td class="xl24" style="border-left: medium none; width: 111pt;" width="148">Estimated End Date</td> <td class="xl24" style="border-left: medium none; width: 91pt;" x:str="Actual End date " width="121">Actual End date </td> <td class="xl24" style="border-left: medium none; width: 165pt;" width="220">Remark</td> </tr> <tr style="height: 40.5pt;" height="54"> <td class="xl25" style="height: 40.5pt; border-top: medium none;" x:num="40395" height="54">05-Aug</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="40403">13-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num=""> 2</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="40405" x:fmla="=B2+C2">15-Aug</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="40408" x:fmla="=A15">18-Aug</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 165pt;" width="220">Actual end date is "END DATE" + No of "offday"and shows next working days</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40396" height="18">06-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40397" height="18">07-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40398" height="18">08-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">offday</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40399" height="18">09-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40400" height="18">10-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40401" height="18">11-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">offday</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40402" height="18">12-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40403" height="18">13-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40404" height="18">14-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40405" height="18">15-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">offday</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40406" height="18">16-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">offday</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40407" height="18">17-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">offday</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; border-top: medium none;" x:num="40408" height="18">18-Aug</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">working</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">　</td> </tr> </tbody></table>
<table x:str="" style="border-collapse: collapse; width: 565pt;" border="0" cellpadding="0" cellspacing="0" width="753"><tbody><tr style="height: 13.5pt;" height="18"><td class="xl23" style="width: 54pt;" x:str="End Date " width="72">
</td><td class="xl23" style="width: 84pt;" width="112">
</td><td class="xl23" style="width: 109pt;" width="145">
</td><td class="xl23" style="width: 91pt;" x:str="Actual End date " width="121">
</td><td class="xl23" style="width: 165pt;" width="220">
</td></tr><tr style="height: 13.5pt;" height="18"> <td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td class="xl23">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 13.5pt;" height="18"><td class="xl23">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### AlphaFrog

##### MrExcel MVP
Try something like this...
Excel Workbook
ABCDE
1Start DateEnd DateNo of "Offday"Estimated End DateActual End date
25-Aug13-Aug215-Aug18-Aug
3
46-Augworking
57-Augworking
68-Augoffday
79-Augworking
810-Augworking
911-Augoffday
1012-Augworking
1113-Augworking
1214-Augworking
1315-Augoffday
1416-Augoffday
1517-Augoffday
1618-Augworking
...
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))
D2=B2+C2
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<br></br>

Last edited:

#### Thiyagu

##### Board Regular
Really thanks for your help...I not yet try , and i think this solution sure work.
I will try next week ..You are genius..
Again thanks for ur help...

Bye have a nice days..

#### Thiyagu

##### Board Regular
Hi,

The formula work only if "Estimated End Date" column fall on offday..Then the formula will show next working date at "Actual End Date".
The formula shows wrong day if "Estimated End Date" column fall on working date..The formula show next working day but i want it to show same working day since it's not a offday.

<table x:str="" style="border-collapse: collapse; width: 508pt;" border="0" cellpadding="0" cellspacing="0" width="677"><col style="width: 64pt;" width="85"> <col style="width: 60pt;" width="80"> <col style="width: 86pt;" width="114"> <col style="width: 107pt;" width="143"> <col style="width: 87pt;" width="116"> <col style="width: 104pt;" width="139"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 64pt;" height="18" width="85">Start Date</td> <td class="xl24" style="width: 60pt;" width="80"> End Date</td> <td class="xl24" style="width: 86pt;" width="114">No of "Offday"</td> <td class="xl24" style="width: 107pt;" width="143">Estimated End Date</td> <td class="xl24" style="width: 87pt;" width="116">Actual End date</td> <td class="xl24" style="width: 104pt;" width="139">Remark</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40395.291666666664" height="18">5-Aug-10</td> <td class="xl25" x:num="40396.291666666664"> 6-Aug-10</td> <td class="xl24" x:num=""> 1</td> <td class="xl25" x:num="40397.291666666664" x:fmla="=C2+B2">7-Aug-10</td> <td class="xl25" x:num="40398">8-Aug-10</td> <td class="xl24">Should show 7 Aug</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40395.291666666664" height="18">5-Aug-10</td> <td class="xl24">working</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40396" height="18">6-Aug-10</td> <td class="xl24">offday</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40397" height="18">7-Aug-10</td> <td class="xl24">working</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40398" height="18">8-Aug-10</td> <td class="xl24">working</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40399" height="18">9-Aug-10</td> <td class="xl24">working</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> </tbody></table>

#### AlphaFrog

##### MrExcel MVP

It seems to be working for me. I used the same formulas. I don't know what to tell you.
Excel Workbook
ABCDEF
1Start DateEnd DateNo of "Offday"Estimated End DateActual End dateRemark
25-Aug-106-Aug-1017-Aug-107-Aug-10Should show 7 Aug
3
45-Aug-10working
56-Aug-10offday
67-Aug-10working
78-Aug-10working
89-Aug-10working
...
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))
D2=B2+C2
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

#### Thiyagu

##### Board Regular
Really sorry maybe i not explain properly to u.Duration start date & End Date is not fixed 24 hours..Example below for 8 hrs..
Really sorry for inconvinent

<table x:str="" style="border-collapse: collapse; width: 713pt;" border="0" cellpadding="0" cellspacing="0" width="949"><col style="width: 91pt;" width="121"> <col style="width: 93pt;" width="124"> <col style="width: 86pt;" width="114"> <col style="width: 107pt;" width="142"> <col style="width: 86pt;" width="115"> <col style="width: 250pt;" width="333"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl22" style="height: 13.5pt; width: 91pt;" height="18" width="121">Start Date</td> <td class="xl22" style="width: 93pt;" width="124">End Date</td> <td class="xl22" style="width: 86pt;" width="114">No of "Offday"</td> <td class="xl22" style="width: 107pt;" width="142">Estimated End Date</td> <td class="xl22" style="width: 86pt;" width="115">Actual End date</td> <td class="xl22" style="width: 250pt;" x:str="Remark " width="333">Remark </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40395.333333333336" height="18">8/5/10 8:00 AM</td> <td class="xl23" x:num="40396.666666666664">8/6/10 4:00 PM</td> <td class="xl22" x:num="">1</td> <td class="xl23" x:num="40397.666666666664" x:fmla="=B2+C2">8/7/10 4:00 PM</td> <td class="xl23" x:num="40398.333333333336">8/8/10 8:00 AM</td> <td class="xl22">Actual End date Should show 8/7/10 4:00 PM</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl22" style="height: 13.5pt;" height="18">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40395.333333333336" height="18">8/5/10 8:00 AM</td> <td class="xl22">working</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40396.333333333336" height="18">8/6/10 8:00 AM</td> <td class="xl22">offday</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40397.333333333336" height="18">8/7/10 8:00 AM</td> <td class="xl22">working</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40398.333333333336" height="18">8/8/10 8:00 AM</td> <td class="xl22">working</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" x:num="40399.333333333336" height="18">8/9/10 8:00 AM</td> <td class="xl22">working</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> </tbody></table>

#### Thiyagu

##### Board Regular

Anyone can help me pls

#### AlphaFrog

##### MrExcel MVP
New formula in E2 and don't put any time values with the dates in A3:A20. Just list the dates.
Excel Workbook
ABCDE
1Start DateEnd DateNo of "Offday"Estimated End DateActual End date
28/5/10 8:00 AM8/6/10 4:00 PM18/7/10 4:00 PM8/7/10 4:00 PM
3
48/5/2010working
58/6/2010offday
68/7/2010working
78/8/2010working
88/9/2010working
...
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))
D2=B2+C2
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<br>

#### Thiyagu

##### Board Regular
Really thanks with u help,.Now i get want i want ..Really appreciate with your help.
<table x:str="" style="border-collapse: collapse; width: 54pt;" border="0" cellpadding="0" cellspacing="0" width="72"><col style="width: 54pt;" width="72"><tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt; width: 54pt;" height="18" width="72">
</td> </tr></table>

Replies
3
Views
59
Replies
5
Views
45
Replies
11
Views
111
Replies
3
Views
43
Replies
3
Views
32