Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
DateDiff in hours , minutes and seconds between two dates should include only working hours Crystal Report
Hi,
I am working on a crystal report to show the Responded and Completed time of work orders based on work order created date and Responded Date. Created date and completed date
I want to display the difference of dates in hours minutes and seconds which should only include working hours
WOrking hours window - Mon - Fri 7:30 am - 5:00 pm
Thanks !
I am working on a crystal report to show the Responded and Completed time of work orders based on work order created date and Responded Date. Created date and completed date
I want to display the difference of dates in hours minutes and seconds which should only include working hours
WOrking hours window - Mon - Fri 7:30 am - 5:00 pm
Thanks !
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IN crystall you would need to perform the calculations
The logic shoukd exclude the first day of the work order and the last when the work order completed the says in between multiplied by 9.5 hours.
Then adding the difference in time between work order entry and end of day. Plus the time diff from start of day till when the work order was completed.
The combination of getting number of days in the wororder....
I.e. Processing wise...........
The logic shoukd exclude the first day of the work order and the last when the work order completed the says in between multiplied by 9.5 hours.
Then adding the difference in time between work order entry and end of day. Plus the time diff from start of day till when the work order was completed.
The combination of getting number of days in the wororder....
I.e. Processing wise...........
Efficient and maintainable would be a sql server scalar udf receiving two dates and returning a value, i suppose!
Good luck with that. The only way you're going to be able to come up with a rock-solid answer is to build a SQL Server Calendar Table , but build it hourly (half hour?) instead of daily like in the article, with a column 'open for business' that identifies if the place is open or not.
Otherwise you could come up with a function that handles the Mon - Fri 7:30 am - 5:00 pm, but it will not handle any exception to that such as staying open and closing early.
Recommend doing a Google Search for 'steve wake sql', as there's a SQL expert named Steve Wake who worked at Chipotle and did a presentation at PASS Summit 2015 on how to pull off a calendar table by hour. Maybe he published some of this presentation for download.
Otherwise you could come up with a function that handles the Mon - Fri 7:30 am - 5:00 pm, but it will not handle any exception to that such as staying open and closing early.
Recommend doing a Google Search for 'steve wake sql', as there's a SQL expert named Steve Wake who worked at Chipotle and did a presentation at PASS Summit 2015 on how to pull off a calendar table by hour. Maybe he published some of this presentation for download.
The formulas from Ken Hamady do work. I have used them in the past. They can be a bit of a pain to get working.
mlmcc
mlmcc
I have the below logic from other reports, it is calculating the hours by excluding the week end (Saturday & sunday)
but not calculating for working hours. Mon - Fri (7:30 am - 5:00 pm)
How can i add the logic to it to calculate time only with working hours
Function CF_getDuration (StartingDate As DateTime, EndingDate As DateTime, Sought As String) As String
DIM SECONDS_PER_DAY:SECONDS_PE
DIM SECONDS_PER_HOUR: SECONDS_PER_HOUR=60*60
DIM SECONDS_PER_MINUTE: SECONDS_PER_MINUTE=60
DIM Days as number,Hours as number, Minutes as number, Seconds as number
DIM TotalSeconds as number
DIM WeekendDayCount as number
DIM NewEndingDate as DateTime
' Get the number of business days between the 2 dates
WeekendDayCount = DateDiff("ww", StartingDate, EndingDate, crSaturday) + DateDiff ("ww", StartingDate, EndingDate, crSunday)
' Set a new end date that removes the business day count
NewEndingDate = EndingDate - WeekendDayCount
'Determine the total number of seconds
Seconds=datediff("s",Start
TotalSeconds=Seconds
'calc days
Days=Seconds \ SECONDS_PER_DAY 'integer division
Seconds=Seconds-Days*SECON
'calc hours
Hours=Seconds \ SECONDS_PER_HOUR 'integer division
Seconds=Seconds-Hours*SECO
'calc minutes
Minutes=Seconds \ SECONDS_PER_MINUTE 'integer division
Seconds=Seconds-Minutes*SE
IF Sought = "d:h:m" THEN
'concatenate
'CF_TEST_DMHS=Cstr(Days,0)
CF_getDuration=Cstr(Days,0
ELSEIF Sought = "d" THEN
' Return total days, and allow fractional numbers
Days = TotalSeconds / SECONDS_PER_DAY
CF_getDuration = CStr(Days,6)
ELSEIF Sought = "h" THEN
' Return total hours, and allow fractional numbers
Hours = TotalSeconds / SECONDS_PER_HOUR
CF_getDuration = CStr(Hours,6)
ELSEIF Sought = "n" THEN
' Return total minutes, and allow fractional numbers
Minutes = TotalSeconds / SECONDS_PER_MINUTE
CF_getDuration = CStr(Minutes,6)
END IF
but not calculating for working hours. Mon - Fri (7:30 am - 5:00 pm)
How can i add the logic to it to calculate time only with working hours
Function CF_getDuration (StartingDate As DateTime, EndingDate As DateTime, Sought As String) As String
DIM SECONDS_PER_DAY:SECONDS_PE
DIM SECONDS_PER_HOUR: SECONDS_PER_HOUR=60*60
DIM SECONDS_PER_MINUTE: SECONDS_PER_MINUTE=60
DIM Days as number,Hours as number, Minutes as number, Seconds as number
DIM TotalSeconds as number
DIM WeekendDayCount as number
DIM NewEndingDate as DateTime
' Get the number of business days between the 2 dates
WeekendDayCount = DateDiff("ww", StartingDate, EndingDate, crSaturday) + DateDiff ("ww", StartingDate, EndingDate, crSunday)
' Set a new end date that removes the business day count
NewEndingDate = EndingDate - WeekendDayCount
'Determine the total number of seconds
Seconds=datediff("s",Start
TotalSeconds=Seconds
'calc days
Days=Seconds \ SECONDS_PER_DAY 'integer division
Seconds=Seconds-Days*SECON
'calc hours
Hours=Seconds \ SECONDS_PER_HOUR 'integer division
Seconds=Seconds-Hours*SECO
'calc minutes
Minutes=Seconds \ SECONDS_PER_MINUTE 'integer division
Seconds=Seconds-Minutes*SE
IF Sought = "d:h:m" THEN
'concatenate
'CF_TEST_DMHS=Cstr(Days,0)
CF_getDuration=Cstr(Days,0
ELSEIF Sought = "d" THEN
' Return total days, and allow fractional numbers
Days = TotalSeconds / SECONDS_PER_DAY
CF_getDuration = CStr(Days,6)
ELSEIF Sought = "h" THEN
' Return total hours, and allow fractional numbers
Hours = TotalSeconds / SECONDS_PER_HOUR
CF_getDuration = CStr(Hours,6)
ELSEIF Sought = "n" THEN
' Return total minutes, and allow fractional numbers
Minutes = TotalSeconds / SECONDS_PER_MINUTE
CF_getDuration = CStr(Minutes,6)
END IF
Did you look at the formulas in the report I added?
Did you look at the formulas that PortletPaul linked to on Ken Hamady's web site?
mlmcc
Did you look at the formulas that PortletPaul linked to on Ken Hamady's web site?
mlmcc
Yes, i did look the formulas in the report but I am getting all negative values in hours.. It doesn't seem to be working correctly.
Double check where you're using the start and end dates. The obvious guess would be that you have them backwards. If you're not sure, post your formulas, or the actual report (the .RPT file), so that we can take a look at them.
Having said that, if you're looking for the difference down to the second, Ken's formulas give you the difference in hours, so that's probably only a partial solution (Unless the result includes fractions of hours. I haven't checked). I didn't look at the report that mlmcc posted, so I don't know if he calculated down to the second.
James
Having said that, if you're looking for the difference down to the second, Ken's formulas give you the difference in hours, so that's probably only a partial solution (Unless the result includes fractions of hours. I haven't checked). I didn't look at the report that mlmcc posted, so I don't know if he calculated down to the second.
James
Below is the formula I am using from the above attached TimeToComplete.rpt
TASKS.OPENDATE = 2017-03-01 14:39:04.000
TASKS.COMPLETED = 2017-03-02 14:40:00.000
Attached is the report pls look for TimeCompleted formula,
I am getting the average in the report
TASKS.OPENDATE = 2017-03-01 14:39:04.000
TASKS.COMPLETED = 2017-03-02 14:40:00.000
Attached is the report pls look for TimeCompleted formula,
I am getting the average in the report
The formula is working. The variable Days is an intermediate value and does NOT correspond to the number of days the WO took.
All I had to do was to uncomment the NumDays conversion to a string and eliminate the Days conversion and it works
This is the new last line of the formula
mlmcc
All I had to do was to uncomment the NumDays conversion to a string and eliminate the Days conversion and it works
This is the new last line of the formula
CStr(NumDays,0) + " days " + CStr(Numhours,0) & ":" + CStr(NumMinutes, "00") & ":" + CStr(NumSeconds,"00");
mlmcc
I need the result in d: h:m which I had in my code before I sent.. If it is only 5 min , formula should return 0:0:05 ,
If it is 65 min then 0:1:05 and if it is more than one day and 1 hour 45 min then 1:1:45
I need other formula to return only hours for the bar graph
Thanks !
If it is 65 min then 0:1:05 and if it is more than one day and 1 hour 45 min then 1:1:45
I need other formula to return only hours for the bar graph
Thanks !
My correction should display it as x days hh:mm:ss
What other formula are you referring to?
The chart will only show the count of the records. The hour values won't enter into it at all.
What are you trying to show with the chart?
mlmcc
What other formula are you referring to?
The chart will only show the count of the records. The hour values won't enter into it at all.
What are you trying to show with the chart?
mlmcc
Is this what you want for the display?
d:hh:mm:ss
mlmcc
d:hh:mm:ss
CStr(NumDays,0) + ":" + CStr(Numhours,"00") & ":" + CStr(NumMinutes, "00") & ":" + CStr(NumSeconds,"00");
mlmcc
I have used the below formula but still i get negative values
Date({TASKS.OPENDATE} = 2017-03-08 19:11:24.000
Date{TASKS.COMPLETED} = 2017-03-10 23:53:27.000
Result in negative values : -17:00:00:-57
DateVar Array Holidays := [
Date (2017,1,1),
Date (2017,2,20),
Date (2017, 5, 29)
];
Local DateVar Start := Date({TASKS.OPENDATE});
Local DateVar End := Date({TASKS.COMPLETED});
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1;
Days := Days + (if DayOfWeek(Start) = 1 then 1 else 0);
Days := Days + (if DayOfWeek(End) = 7 then 1 else 0);
Local NumberVar i;
For i:= 1 to Count (Holidays) do
(
if DayOfWeek ( Holidays(i) ) in 2 to 6 and
Holidays(i) in start to end then
Hol:=Hol+1
);
Days := Weeks + Days - Hol;
TimeVar SetStart := TimeValue("7:30");
TimeVar SetEnd := TimeValue("17:00");
TimeVar StartTime := TimeValue({TASKS.OPENDATE}
TimeVar EndTime := TimeValue({TASKS.COMPLETED
Local NumberVar NumDays;
Local NumberVar NumHours;
Local NumberVar NumMinutes;
Local NumberVar NumSeconds;
NumSeconds := (Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)) * 3600;
//NumDays := NumSeconds \ (9.5 * 60* 60);
//NumSeconds := NumSeconds MOD (9.5 * 60* 60);
NumHours := NumSeconds \ 3600;
NumSeconds := NumSeconds MOD 3600;
NumDays := NumSeconds \ 60;
NumSeconds := NumSeconds MOD 60;
//CStr(NumDays,0) + " days " +
CStr(NumDays,0) + ":" + CStr(Numhours,"00") & ":" + CStr(NumMinutes, "00") & ":" + CStr(NumSeconds,"00");
Date({TASKS.OPENDATE} = 2017-03-08 19:11:24.000
Date{TASKS.COMPLETED} = 2017-03-10 23:53:27.000
Result in negative values : -17:00:00:-57
DateVar Array Holidays := [
Date (2017,1,1),
Date (2017,2,20),
Date (2017, 5, 29)
];
Local DateVar Start := Date({TASKS.OPENDATE});
Local DateVar End := Date({TASKS.COMPLETED});
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1;
Days := Days + (if DayOfWeek(Start) = 1 then 1 else 0);
Days := Days + (if DayOfWeek(End) = 7 then 1 else 0);
Local NumberVar i;
For i:= 1 to Count (Holidays) do
(
if DayOfWeek ( Holidays(i) ) in 2 to 6 and
Holidays(i) in start to end then
Hol:=Hol+1
);
Days := Weeks + Days - Hol;
TimeVar SetStart := TimeValue("7:30");
TimeVar SetEnd := TimeValue("17:00");
TimeVar StartTime := TimeValue({TASKS.OPENDATE}
TimeVar EndTime := TimeValue({TASKS.COMPLETED
Local NumberVar NumDays;
Local NumberVar NumHours;
Local NumberVar NumMinutes;
Local NumberVar NumSeconds;
NumSeconds := (Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)) * 3600;
//NumDays := NumSeconds \ (9.5 * 60* 60);
//NumSeconds := NumSeconds MOD (9.5 * 60* 60);
NumHours := NumSeconds \ 3600;
NumSeconds := NumSeconds MOD 3600;
NumDays := NumSeconds \ 60;
NumSeconds := NumSeconds MOD 60;
//CStr(NumDays,0) + " days " +
CStr(NumDays,0) + ":" + CStr(Numhours,"00") & ":" + CStr(NumMinutes, "00") & ":" + CStr(NumSeconds,"00");
This seems to be working. It returns 1:00:00:56 for the given dates.
DateVar Array Holidays := [
Date (2017,1,1),
Date (2017,2,20),
Date (2017, 5, 29)
];
Local DateTimeVar StartDT := {TASKS.OPENDATE}; // DateTime(2017,03,01, 14,39,04);
Local DateTimeVar EndDT := {TASKS.COMPLETED}; // DateTime(2017,03,02, 14,40,00);
Local DateVar Start := Date(StartDT);
Local DateVar End := Date(EndDT);
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1;
Days := Days + (if DayOfWeek(Start) = 1 then 1 else 0);
Days := Days + (if DayOfWeek(End) = 7 then 1 else 0);
Local NumberVar i;
For i:= 1 to Count (Holidays) do
(
if DayOfWeek ( Holidays[i] ) in 2 to 6 and
Holidays[i] in start to end then
Hol:=Hol+1
);
Days := Weeks + Days - Hol;
TimeVar SetStart := TimeValue("7:30");
TimeVar SetEnd := TimeValue("17:00");
TimeVar StartTime := TimeValue(StartDT);
TimeVar EndTime := TimeValue(EndDT);
Local NumberVar NumDays;
Local NumberVar NumHours;
Local NumberVar NumMinutes;
Local NumberVar NumSeconds;
NumSeconds := (Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)) * 3600;
NumDays := NumSeconds \ (9.5 * 60* 60);
NumSeconds := NumSeconds MOD (9.5 * 60* 60);
NumHours := NumSeconds \ 3600;
NumSeconds := NumSeconds MOD 3600;
NumMinutes := NumSeconds \ 60;
NumSeconds := NumSeconds MOD 60;
CStr(NumDays,0) + ":" + CStr(Numhours,"00") & ":" + CStr(NumMinutes, "00") & ":" + CStr(NumSeconds,"00");
Premium Content
You need an Expert Office subscription to comment.Start Free Trial