Number of Hours between 2 business dates

[smmedeiros]

Looking for some guidance. Does anyone have a formula sample that will calculate the difference in TIME between 2 dates that is business days only? I've been all over the postings. I can easily calculate the difference in days between 2 dates and exclude weekends, but, the results is in number of days. I need my results to be in business hours. (mon-fri to be used as full 24hour days)

Example
Date1 = 04/01/09 16:00
Date2 = 04/08/09 10:00

results should be 114 hours.

Any tips/ideas would be greatly appreciated.

Thanks

 

[lbass]

Please show the formula you are using to arrive at the day total.

-LB

 

[smmedeiros]

Pretty basic.

datediff("d",currentdate,{@twf_start})-
datediff("ww",currentdate,{@twf_start},crSaturday)-
datediff("ww",currentdate,{@twf_start},crSunday)


When I change the first line to 'hours'.. it seems to ignore the next 2 lines to account for weekends???
datediff("h",currentdate,{@twf_start})-
datediff("ww",currentdate,{@twf_start},crSaturday)-
datediff("ww",currentdate,{@twf_start},crSunday)

I've also tried this.. and the results are hours, but, again, does not take into account sat/sun.
datediff("h",currentdate,{@twf_start})-
datediff("h",currentdate,{@twf_start},crSaturday)-
datediff("h",currentdate,{@twf_start},crSunday)

 

[lbass]

Please see Ken Hamady's solution:

http://www.kenhamady.com/form13.shtml

-LB

 

[VAJonD]

Thanks to smmedeiros for the posting and thanks to lbass for the reference to kenhamady solutions. I hope you neither of you mind if I jump in here as I am trying to accomplish the same thing. I have successfully implemented the solution proposed, but find as the formula states, I do receive unpredictable results when my start time is outside my working hours

"One note of caution. The formula assumes that the Start and End dates are business days, and the Start and end times are within your working day. You will get unpredicatble results if you have records that start and end 'after hours'. These records need to be adjusted in a separate formula before they are run through this
formula."

My question is - my records adjusted how?

Thanks.

 

[smmedeiros]

lbass. Thank You for your guidance. Got it working as per the Ken Hamady's solution.

 

[lbass]

VAJonD,

Try adjusting the datetimes by using an if/then to either make them correspond to the last time for the business date if later than closing time, or to the first time of the day if earlier than starting time, by using an if/then statement. Something like:

if hour({table.datetime}) > 17 then
datetime(date({table.datetime}),time(17,0,0)) else
if hour({table.datetime}) < 9 then
datetime(date({table.datetime}),time(9,0,0)) else
{table.datetime}

Then use this in the business hours formula instead of {table.datetime}.

-LB

 

[VAJonD]

Thanks so much lbass,
That got me much closer... but unfortunately I'll have to account for the fact that my times are not in military (23:00:00) format but in serial (11:00:00 PM) format.

I'm thinking I'll need to do the reverse of the TimeSerial function to convert before I drop into the logic, but I can't see what that is. FYI here is a snippet of my code.

if hour({HPD_HelpDesk.Arrival Time}) > 19 Then// Start Time is outside normal hours
datetime(date({HPD_HelpDesk.Arrival Time}),time(19,0,0))else
if hour({HPD_HelpDesk.Arrival Time}) < 7 Then
datetime(date({HPD_HelpDesk.Arrival Time}),time(7,0,0)) else
{HPD_HelpDesk.Arrival Time};

 

[lbass]

Please identify the datatype of your Arrival_Time field. It sounds like it might be a string. If it displays a date as well as a time, please show us how the field displays on its own, outside of the above formula.

-LB

 

[VAJonD]

Although I do not have the level of access to the app needed to view the data type of the field in question... I always assumed it was a dateTime.

Below is a pull of the fields from the app that I am reporting on.

Case ID+ Arrival Date/Time Work In Progress.TIM
-----------------------------------------------------------
HD0000000138110 4/5/2006 8:37:54 AM 4/5/2006 12:43:05 PM
HD0000000138157 4/5/2006 10:58:08 AM 4/5/2006 11:05:37 AM

When one of these dates fall outside my endtime (1900) and therefore have a displayed date of 7:01:00 PM the formula above produces negative times. Again I am thinking I'll need to adjust the times to miltary before running them through the formula and thinking that I'll need the opposite of TimeSerial to accomplish.

Any help you could provide would be fantastic. Thanks again.