Working days between two dates

building reports webintelligence
1

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

KAnil (BOB member since 2008-12-23)

2

Have a look at the post if helps.

Thanks,

Rana :india: (BOB member since 2008-07-08)

3

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.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

4

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> /

anair_bo :us: (BOB member since 2006-12-05)

5

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

iwood (BOB member since 2009-02-11)

6

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:

=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.

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. :smiley:

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 Rathbun :us: (BOB member since 2002-06-06)