Hello Everyone.. I need some help.
I have a spreadsheet which is used to count total number of hours spent on a
Column C: has date/time started
column E: has date/time paused
column G: has date/time resumed
and Column I: has date/time closed
so basically i need it to count the numbers of hours it takes from:
(date/time started) to (date/time paused) + (date/time resumed) to (date/time closed)
so far it is easy.. now
comes the part i cant figure out. sometimes the days shift and I only need to include hours that are between 8am and 5pm and
I need to exclude weekends.
so for example if i have
date/time stated = 8/4/2008 4:00pm
date/time paused = 8/4/2008 4:30 pm
date time resumed = 8/5 /2008 9:am
date time closed = 8/6/2008 10am
the total time should be:
30min + 8hrs + 2 hrs(due to day starting at 8 am and task being complete at 10am) = 10 hours 30 min or 10.5 hours
this was an example of straight week days. but if a date range extends over a weekend I need to exclude those hours.
Can anyone point me in the right direction on how to go about writing either some vba code for this, or if there is a
function already developed in excel that can help me?
Thank you so much in advance.