Reflecting accurate elapsed times

[maddyyikes]

Hi,
I have two fields in the database named {probsummarym1.opentime} and {probsummarym1.closetime} They are datetime fields. I manually calculate the difference between these two times by using the following formula:

numbervar tsecs := datediff("s",{probsummarym1.open.time},{probsummarym1.close.time});
numbervar ndays := truncate(tsecs/86400);
tsecs := remainder(tsecs,86400);
numbervar nhours := truncate(tsecs/3600);
tsecs := remainder(tsecs,3600);
numbervar nmin := truncate(tsecs/60);
tsecs := remainder(tsecs,60);

if nhours-24<=0 then totext(ndays,0)+" days "+totext(nhours,0)+" hours "+ totext(nmin,0)+
" minutes "+totext(tsecs,0)+" seconds "

However, the {probsummarym1.closetime} field is not consistent in its data entry (system generated data for the field). Since it is a date time field, most records contain the date and timestamps, however, there are a few records which contain only the timestamps and they mostly reflect 12:00:00 AM. In these cases the above formula renders an output as follows:
0 days 0 hours 0 minutes 0 seconds
which is not the accurate elapsed time. Please advise as to how should the function be modified so that the elapsed time reflects accurate values irrespective of the date and timestamps not being completely generated by the system at the time of data entry.

 

[lbass]

I think you need to specify what you would want to use for a null close datetime--currentdatetime? Try changing the following variable:

numbervar tsecs;
if isnull({probsummarym1.close.time}) then
tsecs := datediff("s",{probsummarym1.open.time}, currentdatetime) else
tsecs := datediff("s",{probsummarym1.open.time},{probsummarym1.close.time});

-LB