Calculating Non-working Dates and Times

[watermt]

=IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1))

I’m trying to calculate the non-working time accumulated by a process
outside the normal shift of M-F 0630-1500 timeframe. But if all the work is
done on a non-working day (Saturday or Sunday) I get the negative dates or
time symbol ########, or in some cases hours after or before the time above
for M-F. I’ve checked the dates and they’re correct.

Can someone explain possibly other reasons why this may be occurring?

Mike

 

[Fred Smith]

Give us an example of your data (the start and end date/times), and the
result you want to see. What happens if you start outside of working hours,
but finish inside? You'll need to decide on what your definition of "working
hours" is.

Regards,
Fred.

 

[watermt]

Would you like me to email you a sample file? If so, let me know how I can
get that to you?

Mike

 

[Fred Smith]

No. Most people don't like to open files because of the risk of viruses. If
you absolutely have to have someone look at the file, then upload it to a
file hosting website (there are many), then post the link. But don't be
surprised if people don't open the file.

Just show a sample of what you need done. How difficult is it to give a few
examples? Something like:
Start Saturday at 07:00, end Saturday as 12:00 is 5 hours time worked.
Start Saturday at 13:00, end Sunday at 15:00 is ? hours time worked.
Start Sunday at 14:00, end Monday at 12:00 is ? hours time worked.

Only you know what situations you need to handle, and what result you want.

Regards,
Fred.

 

[watermt]

No problem, here we go:

Start Monday 2/23/2009 18:10; End Tuesday 2/24/2009 06:30; 13 hours 20
minutes non-working weekday (M-F) time

Start Thursday 2/12/2009 09:34; End Thursday 2/12/2009 15:29; 5 hours 55
minutes working weekday (M-F) time

Start Wednesday 2/11/2009 23:39; End Thursday 2/12/2009 06:30; 6 hours 51
minutes working weekday (M-F) time

Start Saturday 2/7/2009 15:21; End Monday 2/9/2009 06:30; 39 hours 9 minutes
non-working weekend time

Again, the department I'm working with has normal business hours M-F 06:30
to 15:30.

Thanks again,
Mike

 

[Fred Smith]

This is a lot more complicated than your original post. It seems to me
you're looking to calculate three different things: "working weekday time",
"non-working weekday time" and "non-working weekend time".

As many people won't be following this post, your best bet is to start a new
thread. Be specific in what you need. From what I can see you need to
specify:

1. How do you determine what "time zone" you're in? By the start time? end
time?
2. What do you want the output to look like? If you simply show a time, as
in your original formula, you won't know whether it's "working",
"non-working", etc.
3. What happens if you start in one zone (eg 13:00 on Friday), and end in
another (eg 09:00 Saturday)?

If you cover all the bases in your request, you'll get a workable answer.

Regards,
Fred.

 

[watermt]

Fred,
1. How do you determine what "time zone" you're in? By the start time? end

time?

Answer: We are only dealing with CST

2. What do you want the output to look like? If you simply show a time, as
in your original formula, you won't know whether it's "working",
"non-working", etc.

Answer: Need to know if the event occurred before 0630 or after 1500 each day

3. What happens if you start in one zone (e.g. 13:00 on Friday), and end in
Another (e.g. 09:00 Saturday)?

Answer: Again, we're only dealing with one time zone - CST

I simply need the total amount of time (hh:mm) a process was worked on when
a department has no staff working (their hours are 0630 - 1500 M-F). The
reat of the hospital works 24/7. I'm trying to account for work time
performed by these other disciplines on a particular sub-process (steps) that
must be completed to finish the overall process (i.e., dietary
consultations). Example, a patient might be admitted and seen by a doctor on
the weekend, these are sub-processes. The Dietary department doesn't start
their work until Monday morning at 0630 and ends their shift at 1500 Monday).

If you think I need to re-word my plea for assistance please let me know, I
appreciate your time. I could also do a screen print of my Excel file and
past that picture into an email for anyone willing to take a look!

Mike