Bler
May 14th, 2006, 06:33 AM
Hi! I am creating a report that reads from a database table (named
CRYSTAL) and I need to calculate the time difference between two
columns, IN and OUT. The column values represent the
employees' start/end working hours and are stored as string in the
format hh:mm. The table has thousands of rows.Therefore i would like to subtract the start time from
the end time to find out how many hours and minutes each employee has
been working.
The result format is not important to be strictly in the hh:mm
format,it can be a decimal as well.
My table looks like this:
Empl_code Name IN OUT Date
1234 Mary J. 08:21 17:32 19/04/2006
1234 Mary J. 18:00 19/04/2006
3456 John P. 08:00 17:00 19/04/2006
I get the StartTime and EndTime from a biometric hand reader.
Therefore I need to calculate how many hours and mins each employee has
worked.I am assuming that the employees might punch in and out more
than once each day.Also I need also to provide in case employees forget
to punch in or out correctly thus having only the StartTime or the
EndTime.
I have created a formula to do the task and the code I am using is:
local StringVar sStartTime := {CRYSTAL.IN};
local StringVar sEndTime := {CRYSTAL.OUT};
local DateTimeVar dtStartTime := DateTimeValue(sStartTime);
local DateTimeVar dtEndTime := DateTimeValue(sEndTime);
local NumberVar dur := DateDiff("n", dtStartTime, dtEndTime);
NumberVar hrs;
NumberVar min;
StringVar hhmm;
hrs := Abs(Truncate(Truncate(dur/60)));
min := Abs(Remainder(Truncate(dur/60),60));
hhmm:= ToText(hrs, "0") + ":" + ToText(min, "00");
if (dur < 0) then
hhmm := "-" + hhmm;
However when i run the report it prompts me for an error : Bad date-time format string.Does anyone have any suggestions?Or is there any other way of doing this?I am quite new to SQL and CRYSTAL and it is taking me a while to get
used to
them. Any help will be very appreciated.Thank you for your time!All the best
Bler
CRYSTAL) and I need to calculate the time difference between two
columns, IN and OUT. The column values represent the
employees' start/end working hours and are stored as string in the
format hh:mm. The table has thousands of rows.Therefore i would like to subtract the start time from
the end time to find out how many hours and minutes each employee has
been working.
The result format is not important to be strictly in the hh:mm
format,it can be a decimal as well.
My table looks like this:
Empl_code Name IN OUT Date
1234 Mary J. 08:21 17:32 19/04/2006
1234 Mary J. 18:00 19/04/2006
3456 John P. 08:00 17:00 19/04/2006
I get the StartTime and EndTime from a biometric hand reader.
Therefore I need to calculate how many hours and mins each employee has
worked.I am assuming that the employees might punch in and out more
than once each day.Also I need also to provide in case employees forget
to punch in or out correctly thus having only the StartTime or the
EndTime.
I have created a formula to do the task and the code I am using is:
local StringVar sStartTime := {CRYSTAL.IN};
local StringVar sEndTime := {CRYSTAL.OUT};
local DateTimeVar dtStartTime := DateTimeValue(sStartTime);
local DateTimeVar dtEndTime := DateTimeValue(sEndTime);
local NumberVar dur := DateDiff("n", dtStartTime, dtEndTime);
NumberVar hrs;
NumberVar min;
StringVar hhmm;
hrs := Abs(Truncate(Truncate(dur/60)));
min := Abs(Remainder(Truncate(dur/60),60));
hhmm:= ToText(hrs, "0") + ":" + ToText(min, "00");
if (dur < 0) then
hhmm := "-" + hhmm;
However when i run the report it prompts me for an error : Bad date-time format string.Does anyone have any suggestions?Or is there any other way of doing this?I am quite new to SQL and CRYSTAL and it is taking me a while to get
used to
them. Any help will be very appreciated.Thank you for your time!All the best
Bler