Find offday using formula

T

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.
 
Sort by date Sort by votes
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>
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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 {}.
 
Upvote 0
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>
 
Upvote 0
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>
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Date Range
Replies
1
Views
91
awoohaw
awoohaw
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
234
amolvijay
A
J
  • Question
I’m trying to get a sense of trends for each day of the week and then plot it against a moving average so I can get some context.
Replies
4
Views
153
jacquiejacuinde
J
C
  • Question
Incrementing Data
Replies
1
Views
291
chaboyski
C
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W

Forum statistics

Threads
1,214,428
Messages
6,119,420
Members
448,895
Latest member
omarahmed1

Share this page

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
Back
Top