Calculating the Amount of Business Hours between Two Dates
547979Nov 23 2006 — edited Nov 24 2006Hi All!
I have just started working on a methodology on how to calculate the amount of business hours (8 hour day) between two different dates (mm/dd/yy hh:mm). I'm limited in that there is no calender table and I do not have the permission to create one. :(
Step One:
Calculate the date difference:
Close_Date - Open_Date
Step Two:
Calculate how many weeks between the dates:
TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7))
Step Three:
For any amount of weeks we multiply by 2 to get a weekend count:
TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7)) *2)
Step Four:
Subtract Step Three from Step One to get the amount of days passed which aren't a weekend:
(Wfm_Case.Close_Date - Wfm_Case.Open_Date) - (TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7)) *2)
From here I have a fractional result, which is just the days between the two dates minus any weekends.
This is where I get stuck :( I'm not sure on how to get the total business hours from this fractional result! Any help would be appreciated! I have been ripping my hair out about this one!
Thanks in advance!