Night Shift - Time Duration is a negative 1

[alexlc]

Hi,

I'm usng Crystal 9 & accessing Lotus Notes via an ODBC connection.

I'm calculating shift durations from a form which has the start time & end time of the shift, but doesn't calculate shift duration.

Most shifts start & finish on the same date, but I have some shifts which start at 22.00 & finish at 08.00 the next morning - which is 10 hours, or 600 minutes.

Using the date diff function datediff("n",{@Shift_Start},{@Shift_End}) I get -840.00 as the result. (the start & end are formulas as the shift date & times are separate fileds)

When using the formula in faq767-3543 I get -14:00:00

How do I get it to return the true length, and not interfere with the results that are right?

 

[Madawc]

Are the shift fields just time? If so, you need to combine them with the date. Probably you'll need a formula field that takes the various parts using DatePart and assembles them as a DateTime field. Try displaying first, to be sure you have it right.

If the shift fields are already DateTime, it is probably the wrong DateTime, the end using the previous day's date. You could correct this using DateAdd.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[alexlc]

Argh, yes it is using yesterday's date! I've been looking at it for so long I just don't see it anymore! Thank you so much for lifting the veil, off to play now.