Calculating elapsed time in hours:minutes (minus weekends [48 hours])

B

bigjess007

Hi Everyone,

If this question has been asked before, please accept my apologies a
I've tried doing what others have and can't seem to get it to work.
Here is my problem.

I have two colums of dates/times, I'm reffering to them as columns
and B. Column A is when the 'project' began, Column B is when th
'project' ended. I need to figure out the hours between the start an
end date/times minus the 48 hour weekend. I'm counting all 24 hour
each day during the week.

Also, their is column C where the calculation occurs. Currently i
takes column B minus A and gives the result in hours.

Let me elaborate:
Columns A and B have data entered and displayed like this:
A = 7/16/2004 4:16:31 PM
B = 7/19/2004 1:01:41 PM

C is taking B-A and displaying in [h]:mm format.

The current result displayed in the above example is 68 hours : 4
minutes.

Now, this start date/time started on friday, and ended on monday. S
in this case, I need the formula to automatically take out the 48 hour
in the weekend, so it should only be displaying 20 hours and 4
minutes. However some starts and ends can occur on the weekends. Fo
example, if a start date/time was on a sunday morning and ended on
monday afternoon, I would want the formula to start counting hour
beginning on Monday at 12:00AM and stop at the end (monday afternoon).
Likewise if a start date/time was on a thursday morning and ended on
saturday afternoon, I would want the formula to start counting at th
exact start date/time but end counting hours on friday at 23:59.

How do I write a formula to do all of this? I have several thousan
start/end date/times already in the columns, so I just need to get th
formula to calculate them all correctly, without having to modify th
data (start & end date/times).

THANKS!!!
-Jess
 
B

Biff

Hi Jesse!

Try this. I used:

E15 = start
E16 = stop


=(NETWORKDAYS(E15,E16)-SUM(MOD(E15,INT(E15)),1-MOD(E16,INT
(E16))))

Biff
 
N

Norman Harker

Hi Jesse!

I'll not pretend that this is the best way as it's built up bit by
bit:

=NETWORKDAYS(A1,B1)+MOD(B1,1)-MOD(A1,1)-1+IF(WEEKDAY(A1,3)>4,MOD(A1,1),0)+IF(WEEKDAY(B1,3)>4,1-MOD(B1,1),0)

The first three elements represent the "normal" case of starting and
stopping on working days. The two IF function adjustments represent
the cases of starting or stopping at a weekend.

Not thoroughly tested but prelim results look OK.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top