BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

Working days between two dates


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
KAnil
Forum Member
Forum Member



Joined: 22 Dec 2008

Posts: 8



PostPosted: Sun Dec 28, 2008 7:58 pm 
Post subject: Working days between two dates

Hi,

Does anyone know how to calculate the number of working days between two dates?

Situation is this:
field -Send Date
field - Received Date

Need to calculated number of working days (M,T,W,T,F) between 2 dates avoiding weekends.


Cheers,
Anil_08
Back to top
Rana
Principal Member
Principal Member



Joined: 08 Jul 2008

Posts: 234
Location: Dallas, TX


flag
PostPosted: Sun Dec 28, 2008 11:55 pm 
Post subject: Re: Working days between two dates

Have a look at the post if helps.

http://www.forumtopics.com/busobj/viewtopic.php?t=73436

Thanks,

_________________
|Rana|BI Consultant|BOCP-BOE XIR2|Oracle 9i|Teradata|
Dreams unlimited
Back to top
Omkar Paranjpe
Forum Enthusiast
Forum Enthusiast



Joined: 13 Feb 2006
ASUG Icon
Posts: 1898
Location: Minneapolis, MN


flag
PostPosted: Mon Dec 29, 2008 12:13 am 
Post subject: Re: Working days between two dates

Hi,
You can build this using the calendar table.

In the calendar table, have one column which tells whether the day is working day or not (i.e. including the case of week end or holiday).

Say you build a logic that puts 0 against a date row when the day is the saturday or sunday. Similarly, using manual way, you can enter the 0s for the holiday.

For working day put 1

Then join the fact table with this claendar table on the date objects basis. and just do the sum on this object to calculate the working days between the objects.

_________________
Regards,
Omkar Paranjpe.
Back to top
anair_bo
Principal Member
Principal Member



Joined: 05 Dec 2006

Posts: 255
Location: Philly, PA


flag
PostPosted: Mon Dec 29, 2008 1:37 pm 
Post subject: Re: Working days between two dates

Use this below sql.. It will give you number of days betweeen 2 days excluding Saturday and Sunday

I had found it in oracle site sometime back.


START_DT END_DT AGE WORK_DAYS(START_DT,END_DT)
--------- --------- ---------- --------------------------
13-DEC-02 18-DEC-02 5 3
17-DEC-02 19-DEC-02 2 2
18-DEC-02 23-DEC-02 5 3
26-DEC-02 28-DEC-02 2 1



SQL> get workingdays
1 select
2 start_dt,
3 end_dt,
4 trunc(end_dt - start_dt) age,
5 (trunc(end_dt - start_dt) -
6 (
7 (case
8 WHEN (8-to_number(to_char(start_dt,'D') )) > trunc(end_dt -
start_dt)+1

9 THEN 0
10 ELSE
11 trunc( (trunc(end_dt - start_dt) -
(8-to_number(to_char(start_dt,'D') ))) / 7 ) + 1
12 END) +
13 (case
14 WHEN mod(8-to_char(start_dt,'D'),7) > trunc(end_dt - start_dt)-1
15 THEN 0
16 ELSE
17 trunc( (trunc(end_dt-start_dt) -
(mod(8-to_char(start_dt,'D'),7)+1)) / 7 ) + 1

18 END)
19 )
20 ) workingdays
21* from date_test
SQL> /
Back to top
iwood
Forum Member
Forum Member



Joined: 11 Feb 2009

Posts: 3



PostPosted: Wed Feb 11, 2009 12:53 am 
Post subject: Re: Working days between two dates

You can give this a go, took some time to test but is 99% robust howver does not take into account holidays just excludes working days

=Floor((DaysBetween([start_date];[end_date]))-Truncate((DayNumberOfWeek([start_date])+DaysBetween([start_date];[end_date])) /7 ;0)*2)+1

Depending on timezones etc you may need to apply a relative date to the [start_date] and [end_date]

At the risk of teaching people to suck eggs please make sure you relace start_date and end_date with your own variables or ven easier create a variable for each called start_date and end_date then create a third variable and just past the formula and it will just work
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22124
Location: Dallas, Texas


flag
PostPosted: Fri Jul 03, 2009 11:18 am 
Post subject: Re: Working days between two dates

iwood wrote:
=Floor((DaysBetween([start_date];[end_date]))-Truncate((DayNumberOfWeek([start_date])+DaysBetween([start_date];[end_date])) /7 ;0)*2)+1

I found this post a few days ago and just got around to testing it. It does not appear to work in all cases. I have sales dates and invoice dates in a table, and I returned a sample of 250 dates where the invoice date > sales date, and the sales date was on or after 5/15/2009. I entered the formula as provided in Webi, then copied the results to Excel. I then calculated the business days between the two dates manually and compared the results. Here are some sample results.

5/15/2009 is Friday, and 5/29/2009 is a Friday two weeks away. This formula returns 11. That is correct, assuming you count the starting Friday as one of the business days. (In other words, today - today is counted as one business day, not zero. The formula behaved as I expected it to.)

5/16/2009 is Saturday, and 5/20/2009 is the following Wednesday. This formula returns 3 days, which is correct. There are 3 business days between Saturday and Wednesday (counting Mon, Tue, and Wed only).

5/17/2009 is Sunday, and 5/22/2009 is the following Friday. The formula returns 4 and should return 5. There are five business days in a week, and Sun - Fri returns that entire range, so the value should be 5. After further investigation, any row where the start date was a Sunday failed.

That would be okay, as I could put an exception case in place to handle every range that started on a Sunday. However, I did find one case where a row failed when the start date was not a Sunday, but instead was a Monday.

Here is the exact formula I used for testing:
Code:
=Floor((DaysBetween([Sales Order Date];[Sales Order Invoice Date])) - Truncate((DayNumberOfWeek([Sales Order Date]) + DaysBetween([Sales Order Date];[Sales Order Invoice Date])) /7 ;0)*2)+1

And here is the data I used. The columns in this table are the start date, the end date, the value returned by the formula, the expected value, the difference or error, and what day (name) the range started with. I have marked the rows with calculation errors. Note that every row where the start date was Sunday was off by -1, and there was one other row that was off by +1 and started on a Monday.
Code:
Start Date      End Date        Calc    Actual  Error   Start Day
5/15/2009       5/29/2009       11      11      0       Fri
5/16/2009       5/20/2009       3       3       0       Sat
5/17/2009       5/22/2009       4       5       -1      Sun ***
5/17/2009       6/1/2009        10      11      -1      Sun ***
5/18/2009       5/25/2009       6       6       0       Mon
5/18/2009       5/26/2009       7       7       0       Mon
5/19/2009       5/28/2009       8       8       0       Tue
5/19/2009       6/10/2009       17      17      0       Tue
5/20/2009       5/20/2009       1       1       0       Wed
5/21/2009       5/28/2009       6       6       0       Thu
5/21/2009       6/1/2009        8       8       0       Thu
5/24/2009       5/28/2009       3       4       -1      Sun ***
5/25/2009       5/26/2009       2       2       0       Mon
5/26/2009       6/4/2009        8       8       0       Tue
5/27/2009       6/11/2009       12      12      0       Wed
5/28/2009       5/28/2009       1       1       0       Thu
5/29/2009       6/8/2009        7       7       0       Fri
5/31/2009       6/4/2009        3       4       -1      Sun ***
6/1/2009        6/11/2009       9       9       0       Mon
6/1/2009        6/20/2009       16      15      1       Mon ***
6/2/2009        6/8/2009        5       5       0       Tue
6/3/2009        6/3/2009        1       1       0       Wed
6/4/2009        6/8/2009        3       3       0       Thu
6/4/2009        6/10/2009       5       5       0       Thu

After this I did a bit more research and found out that any row ending on Saturday was +1. Well, unless the same range started on Sunday, at which point the Sunday error of -1 cancelled out the Saturday error of +1 and I got the correct result. icon_biggrin.gif

In any case, I plan to investigate this formula further as it does seem to do a good job in most cases. But to anyone using this formula, I would suggest that you test it thoroughly as it did not return the correct results in my case for all rows.

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
• 2018-09-13 Copying Content Is Not A Complement
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0299 seconds using 17 queries. (SQL 0.0026 Parse 0.0009 Other 0.0265)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo