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.
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
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>
 

Some videos you may like

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
Joined
Sep 2, 2009
Messages
16,275
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
Joined
Nov 21, 2009
Messages
131
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
Joined
Nov 21, 2009
Messages
131
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>
Thanks in advance
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,275

ADVERTISEMENT

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
Joined
Nov 21, 2009
Messages
131
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
Joined
Nov 21, 2009
Messages
131

ADVERTISEMENT

Anyone can help me pls
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,275
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
Joined
Nov 21, 2009
Messages
131
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,484
Messages
5,523,218
Members
409,505
Latest member
anmedia

This Week's Hot Topics

Top