Solved

Difference of Two Date/Time

Posted on 2003-12-02
18
397 Views
Last Modified: 2013-12-18
I have two fields (both of date and time type);
ctimeo - the time and date the specific problem is reported
closedtime - the time and date the specific problem is resolved

ex.       ctimeo = 12/02/2003 05:20:57 PM
      closedtime = 12/05/2003 08:10:33 AM

i want to know the following:

a) The difference (day, hours, minutes)between the two to compute for the time lapsed while resolving the problem.

b) The difference bet the two not including nights and weekends. It should only count from 8:00AM to 5:30PM

This is kindda urgent guys. Hope to hear from you soon. Thanks in advance :)
0
Comment
Question by:macjacinto
  • 5
  • 5
  • 4
  • +1
18 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 9856918
Try this:
FIELD starttime_d:=starttime_d;
FIELD endtime_d:=endtime_d;
FIELD totaltime_x:=totaltime_x;
X := endtime_d - starttime_d;
Y := @Modulo(X;3600)/60;
Z :=@Round((X/3600) - (1/2));
Minutes:=@If(Z>=1;Z*60;Z);
res1:=Minutes+@Integer(Y);
result:=@If(starttime_d!="" & endtime_d!="";res1;"0");
@SetField("totaltime_x";result)
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 9856927
the result will give in minutes:
0
 
LVL 14

Assisted Solution

by:p_partha
p_partha earned 50 total points
ID: 9859153
ClosedD:=@Date(ctimeo);
ClosedT:=@Time(ctimeo);
OpenD:=@Date(closedtime);
OpenT:=@Time(closedtime);
ClosedDA:=@Adjust(ClosedD;0;0;@If(@Weekday(ClosedD)=1;-2;@Weekday(ClosedD)=7;-1;0);0;0;0);
OpenDA:=@Adjust(OpenD;0;0;@If(@Weekday(OpenD)=1;-2;@Weekday(OpenD)=7;-1;0);0;0;0);
ClosedTA:=@If(ClosedT > [05:30:00 PM]|@Weekday(ClosedD)=1:7;[05:30:00 PM];ClosedT < [08:00:00 AM];[08:00:00 AM];ClosedT);
OpenTA:=@If(OpenT > [05:30:00 PM]|@Weekday(OpenD)=1:7;[05:30:00 PM];OpenT<[08:00:00 AM];[08:00:00 AM];OpenT);
ttime := ClosedDA - OpenDA;
days := @Integer(ttime/(60*60*24));
weeks:=@Integer(days/7);
cday:=@Weekday(ClosedDA);
oday:=@Weekday(OpenDA);
weekdel:=weeks*2;
wkadj:=@If(cday>=oday;0;2);
hradj:=@If(ClosedTA>=OpenTA;0;1);
adjdays:=days-weekdel-wkadj-hradj;
daytext:=@If(@IsError(adjdays);"";ctimeo != "" & closedtime != "" & adjdays>1; @Text(adjdays) +" days ";adjdays=1;@Text(adjdays) +" day ";"");
ttime1 := @If(ClosedTA >= OpenTA;ClosedTA - OpenTA;ClosedTA - OpenTA+32000);
hours := ttime1/(60*60);
exseconds:=@Modulo(ttime1;60*60);
minutes := exseconds/60;
seconds:=@Modulo(exseconds;60);
FIELD timetext:=@If(Ctimeo!= "" & closedtime != ""; @Text(@Integer(hours)) + " hrs " + @Text(@Integer(minutes)) + " min " + @Text(seconds) + " sec"; "");
daytext+timetext


try this i took it from my goodies...Hope this helps

0
 
LVL 14

Expert Comment

by:p_partha
ID: 9859406
btw timetext is the variable where the resuylt is displayed....


Partha
0
 
LVL 14

Expert Comment

by:p_partha
ID: 9860034
If you want to use lotusscript (which is better control to debug), then use this...

call the function like this :

      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Set uidoc =ws.currentdocument
      Set doc = uidoc.document
      Dim nt As New notesdatetime(doc.ctimeo(0))
      Dim nt1 As New notesdatetime(doc.closedtime(0))




Function lib_BusinessHours(ndt_StartDate As NotesDateTime, ndt_EndDate As NotesDateTime) As Double
      
      If ndt_EndDate.TimeDifference(ndt_StartDate) < 0 Then
            Messagebox "End date is before start date. Cannot continue.", 16, "Error"
            lib_BusinessHours = 0
            Exit Function
      End If
      
      If ndt_StartDate.DateOnly = ndt_EndDate.DateOnly Then
            lib_BusinessHours = Round(ndt_EndDate.TimeDifference(ndt_StartDate)/3600,1)
            Exit Function
      End If
      
      
      Set ndt_Temp = New NotesDateTime(ndt_StartDate.DateOnly & " 5:30 PM")
      d_HoursFirstDay = ndt_Temp.TimeDifference(ndt_StartDate)/3600
      If d_HoursFirstDay < 0 Then d_HoursFirstDay = 0
      Set ndt_Temp = New NotesDateTime(ndt_EndDate.DateOnly & " 8:00 AM")
      d_HoursLastDay = ndt_EndDate.TimeDifference(ndt_Temp)/3600
      If d_HoursLastDay < 0 Then d_HoursLastDay = 0
      
      d_HoursBetween = 0
      
      Do
            Call ndt_StartDate.AdjustDay(+1)
            
            If ndt_StartDate.DateOnly = ndt_EndDate.DateOnly Then Exit Do
            
            If Weekday(ndt_StartDate.DateOnly) <> 1 And Weekday(ndt_StartDate.DateOnly) <> 7 Then
                  
                  
                  
                  d_HoursBetween = d_HoursBetween + 9
                  
            End If
      Loop
      Msgbox  Cstr(Round(d_HoursFirstDay + d_HoursBetween + d_HoursLastDay  ,1) )+" hrs"
End Function

Partha
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 400 total points
ID: 9863359
totalInSeconds = closedtime - ctimeo;
d_minute := 60;
d_hour := 60 * d_minute;
d_day := d_hour * 24;
fullDays := @Integer(totalInSeconds/d_day);
daysRemainderInSeconds := totalInSeconds - fullDays * d_day;
fullHours := @Integer(daysRemainderInSeconds / d_hour);
hoursRemainderInSeconds := daysRemainderInSeconds - fullHours * d_Hour;
fullMinutes := @Integer(hoursRemainderInSeconds / d_minute);
fullSeconds := hoursRemainderInSeconds - fullMinutes * d_minute;
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds); ":")

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9863440
That's the elapsed time in d:h:m:s.  Now, if we want to do this excluding nights and weekends...

firstDayThrough5 := [5:00 PM] - @Time(ctimeo);
firstDaySignificantSeconds := @If(firstDayThrough5 > 0; firstDayThrough5 ; 0);
lastDayPast8 := @Time(closedTime) - [8:00 AM];
lastDaySignificantSeconds := @If(lastDayPast8 > 0; lastDayPast8 ; 0);
firstFullDay := @Date(@Adjust(ctimeo; 0; 0; 1; 0; 0; 0));
lastFullDay := @Date(@Adjust(closedTime; 0; 0; 1; 0; 0; 0));
fullDateRange := @TextToTime(@Text(firstFullDay) + " - " + @Text(lastFullDay));
allDates := @TextToTime(@Explode(fullDateRange));
workDays := @Replace(@Text(@WeekDay(allDates)); "1" : "7"; "");
fullWorkDayCount := @Elelemnts(@Trim(workDays));
fullWorkDaySeconds := ([5:00 PM] - [8:00 AM]) * fullWorkDayCount;

totalInSeconds = firstDaySignificantSeconds + lastDaySignificantSeconds + @If(firstFullDay < closedTime & lastFullDay > ctimeo; fullWorkDaySeconds; 0);

d_minute := 60;
d_hour := 60 * d_minute;
d_day := d_hour * 24;
fullDays := @Integer(totalInSeconds/d_day);
daysRemainderInSeconds := totalInSeconds - fullDays * d_day;
fullHours := @Integer(daysRemainderInSeconds / d_hour);
hoursRemainderInSeconds := daysRemainderInSeconds - fullHours * d_Hour;
fullMinutes := @Integer(hoursRemainderInSeconds / d_minute);
fullSeconds := hoursRemainderInSeconds - fullMinutes * d_minute;
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds); ":")

0
 

Author Comment

by:macjacinto
ID: 9873497
ctimeo = 12/04/2003 06:40:08 PM
closedtime = 12/05/2003 06:38:49 PM

the result is 0990

why is that?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 9879232
hey mac,
Is this for column in a view ur asking for?

As I told u before, what happened to Document Save Conflict?
If it is resolved close that question.

If not, let us know where u get error.

Qwaletee, Partha and other experts,
No body asked where this code is going to implement. Since I saw the application, this will be in a view column.

Mac, should respond where he/she wants to implement this code.

-Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:macjacinto
ID: 9880278
i tried it in both view and field, the error "incorrect data type or @function: number expected" appeared.

madheeswar, i already closed the document save conflict question. tnx a lot.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 9880783
ur field values are empty. Check it first.
Add if conditions...
0
 
LVL 14

Expert Comment

by:p_partha
ID: 9885525
Hey macjacinto
 did u try my code... it works for me here... let me know


Partha
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9895260
Typo:
totalInSeconds = closedtime - ctimeo;
...should be...
totalInSeconds := closedtime - ctimeo;

It returns 0:23:58:41
0
 

Author Comment

by:macjacinto
ID: 9901276
p_partha:

I tried the code you gave and given the dates 12/09/2003 08:47:26 AM and 12/19/2003 08:47:22 AM, it returned a value of 0 hrs 0 min 4 sec. It did not count the days which should also be a part of the return value

madheeswar:

I need the code in days, hours, mins and sec, not just in minutes

qwaletee:

it returned the correct value but when i tried to add the text "days", "hours", "min" & "sec" instead of the ":", i got the "incorrect error" again

and in  the code exluding nights & wkend, it gives out the "incorrect data type error" also even if i already changed the typo error @elements
0
 
LVL 19

Assisted Solution

by:madheeswar
madheeswar earned 50 total points
ID: 9901308
For Qwaletee Formula in the last line:
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds); ":")

make it to this and try
res:=@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds); ":");
@text(fulldays)+"day/s"+@text(fullHours)+"hours"+@text(fullMinutes)+"min"+@text(fullseconds)+"sec"

Just try the above and let us know.

-Thanks
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9901506
macjacinto,
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds + " " " + "days" : "hours" : "minutes" : "seconds"); ",")

I'll lokk into the "minus weekends" version
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9901521
Yuo didn't read my earlier correction.  The problem with the second formula ("minus weekends") s in the oine:

totalInSeconds = firstDaySignificantSeconds + lastDaySignificantSeconds + @If(firstFullDay < closedTime & lastFullDay > ctimeo; fullWorkDaySeconds; 0);

It shoudl be :=, not = ...

totalInSeconds := firstDaySignificantSeconds + lastDaySignificantSeconds + @If(firstFullDay < closedTime & lastFullDay > ctimeo; fullWorkDaySeconds; 0);
0
 

Author Comment

by:macjacinto
ID: 9902111
i pasted the code below and this error appears: An operator or semicolon was expected but none was encountered: "

totalInSeconds := closedtime - ctimeo;
d_minute := 60;
d_hour := 60 * d_minute;
d_day := d_hour * 24;
fullDays := @Integer(totalInSeconds/d_day);
daysRemainderInSeconds := totalInSeconds - fullDays * d_day;
fullHours := @Integer(daysRemainderInSeconds / d_hour);
hoursRemainderInSeconds := daysRemainderInSeconds - fullHours * d_Hour;
fullMinutes := @Integer(hoursRemainderInSeconds / d_minute);
fullSeconds := hoursRemainderInSeconds - fullMinutes * d_minute;
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds + " " " + "days" : "hours" : "minutes" : "seconds"); ",")

i pasted madheeswar's addition to qwaletee's formula and it worked

tnx guys!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now