on 04-06-2015 9:42 PM
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"))
))
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.