# 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 :)
###### 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.

Commented:
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)
Commented:
the result will give in minutes:
Commented:
ClosedD:=@Date(ctimeo);
ClosedT:=@Time(ctimeo);
OpenD:=@Date(closedtime);
OpenT:=@Time(closedtime);
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;
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

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

Partha
Commented:
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"
Exit Function
End If

If ndt_StartDate.DateOnly = ndt_EndDate.DateOnly Then
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

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
Commented:
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); ":")

Experts Exchange Solution brought to you by

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

Commented:
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); ":")

Author Commented:
ctimeo = 12/04/2003 06:40:08 PM
closedtime = 12/05/2003 06:38:49 PM

the result is 0990

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

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

It returns 0:23:58:41
Author 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

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
Commented:
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
Commented:
macjacinto,
@Implode(@Text(fullDays : fullHours : fullMinutes : fullSeconds + " " " + "days" : "hours" : "minutes" : "seconds"); ",")

I'll lokk into the "minus weekends" version
Commented:
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);
Author 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"); ",")