Difference of Two Date/Time

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 :)
macjacintoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
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
madheeswarCommented:
the result will give in minutes:
0
p_parthaCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

p_parthaCommented:
btw timetext is the variable where the resuylt is displayed....


Partha
0
p_parthaCommented:
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
qwaleteeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qwaleteeCommented:
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
macjacintoAuthor Commented:
ctimeo = 12/04/2003 06:40:08 PM
closedtime = 12/05/2003 06:38:49 PM

the result is 0990

why is that?
0
madheeswarCommented:
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
macjacintoAuthor Commented:
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
madheeswarCommented:
ur field values are empty. Check it first.
Add if conditions...
0
p_parthaCommented:
Hey macjacinto
 did u try my code... it works for me here... let me know


Partha
0
qwaleteeCommented:
Typo:
totalInSeconds = closedtime - ctimeo;
...should be...
totalInSeconds := closedtime - ctimeo;

It returns 0:23:58:41
0
macjacintoAuthor Commented:
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
madheeswarCommented:
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
qwaleteeCommented:
macjacinto,
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds + " " " + "days" : "hours" : "minutes" : "seconds"); ",")

I'll lokk into the "minus weekends" version
0
qwaleteeCommented:
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
macjacintoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.