cancel
Showing results for 
Search instead for 
Did you mean: 

How to eliminate weekends with this date range

Former Member
0 Kudos

Hi Folks,

I got below code to find Seconds between two date like

Starting Date: 01/03/2015 10:01:25

Ending Date: 30/03/2015   09:20:14

I want to eliminate the weekends under this given date and calcualte the total numbers of days between two dates

With below code I can able to calucalte number of seconds between two dates

=If(IsError(DaysBetween([Created on] ;[Modified
Date]) * 86400

+

(

ToNumber(FormatDate([Modified Date];"HH")) * 3600 +
ToNumber(Left(FormatDate([Modified Date] ;"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Modified Date] ;"ss"))

)

-

(

ToNumber(FormatDate([Created on] ;"HH")) * 3600 +

ToNumber(Left(FormatDate([Created on] ;"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Created on] ;"ss"))

));0;



DaysBetween([Created on] ;[Modified Date]) * 86400

+

(

ToNumber(FormatDate([Modified Date];"HH")) * 3600 +

ToNumber(Left(FormatDate([Modified Date] ;"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Modified Date] ;"ss"))

)

-

(

ToNumber(FormatDate([Created on] ;"HH")) * 3600 +

ToNumber(Left(FormatDate([Created on] ;"mm:ss") ;2)) * 60 +

ToNumber(FormatDate([Created on] ;"ss"))

))

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi Ganesh,

Thanks for your reply, I wont get expected result along along with time and date.

My code looks like this.

var 1=ToDate([Creation Date] ; "dd/MM/yyyy HH:mm:ss")

var2=ToDate([Modified Date]  ; "dd/MM/yyyy HH:mm:ss")

use this formula for number of  day minus weekened day

=Round( DaysBetween( [Todate];[Fromdate] )/7;0)*2+If DayName(First([Todate]))="Sunday" Then 1 ElseIf DayName(First([Todate]))="Saturday" Then 2

It wont work for me.

Please suggest me how to code this.

Former Member
0 Kudos

var Fromdate=ToDate("01/03/2015 10:01:25";"dd/MM/yyyy HH:mm:ss")

var ToDate=ToDate("30/03/2015   09:20:14";"dd/MM/yyyy HH:mm:ss")

use this formula for number of  day minus weekened day

=Round( DaysBetween( [Todate];[Fromdate] )/7;0)*2+If DayName(First([Todate]))="Sunday" Then 1 ElseIf DayName(First([Todate]))="Saturday" Then 2

Former Member
0 Kudos

Hi Raju,

Thanks for your mail.

Its not working for me to fix along with date and time.

You used date as a static but I need dynamic values.

I have below fields with date function, Creation Date and Creation Time

Modified Date and modified Time.

I want to get exact result for this after eliminating weekends

Kindly suggest me if you have any idea how to hard code.

Former Member
0 Kudos

Just try following formula

Please do some modifications if required as I don't have software right now.

Diff = DaysBetween(starting date,ending date)

week_from_date = week(Startin_date)

week_ending_Date = week(ending_date)

Holidays=if (DayName(Startin Date)= "Sunday" and DayName(Ending Date) = "Sunday")

Then

floor(diff/7)+1

elsif (DayName(Startin Date)= "Sunday" )

then

floor(diff/7)+1

elsif(DayName(Ending Date) = "Sunday")

then

floor(diff/7)+1

else

week_from_date - week_ending_Date

Thanks,
swapnil

Former Member
0 Kudos

Hi Swapnil,

In your save i can get the date range, but problem is I want both time and date difference.

Kindly suggest me how to get output with date differece as well.

Input :

Creatation Date/Time         ModifiedDate/ Time

01/10/2010        10:30:15          10/12/2012  9:30:00

15/10/2010        09:10:25          20/10/2010  15:15:40

25/10/2010         12:00:10          15/11/2010  7:24:10

I tried with your formular earlier and I'm getting time difference are negative.

Kindly suggest me how to days along with time after eliminating weekends.

thanks

ram

Former Member
0 Kudos

Hi Ram,

Let's solve problem one by one.

First try to get the day difference by just applying my formula and let me know whether it is right ot wrong?

then I will let you know the logic for time also.

Thanks,

Swapnil

Former Member
0 Kudos

Hello ram , check the below.

BOBJ Tricks: Workdays Between Two Dates in Webi

Might be help ful.

Former Member
0 Kudos

Swapnil,

Kindly once again send me a proper code for my requirement.

1. I wan to find the exact difference for two date function after eliminating weekends.

Kindly help me its very urgent.

With your codes I can get difference between two dates and Even I done the difference between date which I'm facing negative values and its not proper data.

Former Member
0 Kudos

Hi ,

      This will elimate all week ends . ram check this Sql.

    =RelativeDate([Date Ordered];Floor(DayNumberOfWeek([Date Ordered])/6)*(8-DayNumberOfWeek([Date Ordered]))) to eliminateweek ends

Regards

Dinesh

Former Member
0 Kudos


Hi Dinesh,

If I take seperate time dimension  then the time difference are changing like below.

Creation Time   Modified Time

10:30:05               09:30:10

09:15:30              15:00:01

20:0015               12:15:10

Output looks like this which I don't want.

Former Member
0 Kudos

just right click on this column . select number formate. select values to positive