Solved

Difference of Two Date/Time

Posted on 2003-12-02
18
394 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

20 Experts available now in Live!

Get 1:1 Help Now