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

--
Message posted from http://www.ExcelForum.com

Hi Jesse!

Try this. I used:

E15 = start
E16 = stop


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

Biff

>-----Original Message-----
>Hi Everyone,
>
>If this question has been asked before, please accept my
apologies as
>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 A
>and B. Column A is when the 'project' began, Column B is
when the
>'project' ended. I need to figure out the hours between
the start and
>end date/times minus the 48 hour weekend. I'm counting
all 24 hours
>each day during the week.
>
>Also, their is column C where the calculation occurs.
Currently it
>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 : 45
>minutes.
>
>Now, this start date/time started on friday, and ended on
monday. So
>in this case, I need the formula to automatically take
out the 48 hours
>in the weekend, so it should only be displaying 20 hours
and 45
>minutes. However some starts and ends can occur on the
weekends. For
>example, if a start date/time was on a sunday morning and
ended on a
>monday afternoon, I would want the formula to start
counting hours
>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 a
>saturday afternoon, I would want the formula to start
counting at the
>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 thousand
>start/end date/times already in the columns, so I just
need to get the
>formula to calculate them all correctly, without having
to modify the
>data (start & end date/times).
>
>THANKS!!!
>-Jesse
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>

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.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(E-Mail Removed)
"bigjess007 >" <<(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Hi Everyone,
>
> If this question has been asked before, please accept my apologies
> as
> 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 A
> and B. Column A is when the 'project' began, Column B is when the
> 'project' ended. I need to figure out the hours between the start
> and
> end date/times minus the 48 hour weekend. I'm counting all 24 hours
> each day during the week.
>
> Also, their is column C where the calculation occurs. Currently it
> 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 : 45
> minutes.
>
> Now, this start date/time started on friday, and ended on monday.
> So
> in this case, I need the formula to automatically take out the 48
> hours
> in the weekend, so it should only be displaying 20 hours and 45
> minutes. However some starts and ends can occur on the weekends.
> For
> example, if a start date/time was on a sunday morning and ended on a
> monday afternoon, I would want the formula to start counting hours
> 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
> a
> saturday afternoon, I would want the formula to start counting at
> the
> 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 thousand
> start/end date/times already in the columns, so I just need to get
> the
> formula to calculate them all correctly, without having to modify
> the
> data (start & end date/times).
>
> THANKS!!!
> -Jesse
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>