Calculate the # of Non-working hours in timeframe 1

[mickyjune26]

Hi,
I have two columns:
Column A Column B Column C
5/5/2004 8:32 AM 5/6/2004 8:32 AM 15:00
5/4/2004 8:17 AM 5/7/2004 8:17 AM 30:00
5/4/2004 8:59 AM 5/5/2004 2:59 PM 0:00
5/4/2004 9:06 AM 5/5/2004 10:06 AM 15:00
5/4/2004 9:11 AM 5/10/2004 9:11 AM 101:00
5/19/2004 2:37 PM 5/19/2004 2:43 PM 0:00


Automation Goal:
Column C = B - A, but only include [hours]:minutes that are not M-F, 8-5 (working hours).

Notice the second-to-last date set spans a weekend. Non-business weekend hrs should be added to Column C's total also.

(Note: Column C was manually calculated, so it could be off. I double-checked, and it should be right. Because this process is currently manually done, there will probably be mistakes in the reports)

Thanks a lot,
Micky

Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[macropod]

If it can be assumed that:
1) The non-work hours for each weekday are 15 (from 5pm through to 8am);
2) Columns A & B never include weekends; and
3) You only want to calculate the # hours between 5pm through to 8am on weekdays, plus the 48 hours for any weekend,
then the hours actually worked on each day makes no difference. In that case, you could use:
=(INT(B1)-INT(A1))*15/24+INT((INT(B1)-INT(A1)+(MOD(INT(B1)-2,7)<MOD(INT(A1)-2,7)))/7)*2*9/24
in C1, copied down as far as needed.

For your Column A & B data, the proposed formula in Column C would return:
15:00, 45:00, 15:00, 15:00, 108:00, 0:00
rather than the values in your post. If the above assumptions are not correct, and/or the values in your post are correct, please explain how you arrived at them. For example, if you're trying to tally all hours not worked, how to you determine the hours worked where and interval of more than one weekday is involved?

Cheers

 

[mickyjune26]

It works! As you can see, my manual posts were terribly off.

Let's have the machine do the work for us.

One small comment, we will need to make sure the formatting on column "C" is set to custom and [h].

This does not calculate for minutes, but we don't require minutes in our example, since non-working hours will always be a whole #.

Thanks again,

Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[mickyjune26]

Oh, we have a small hitch. The formula didn't work over this last weekend for these times:
5/21/2004 1:26 5/24/2004 8:31 = 45 (it should be 63 (7 hrs friday, 24 sat, 24 sun, and 7 mon)

It's as if it doesn't know when the weekends are occuring, so the only time the full weekend it calculated is if the start and stop time are longer than 5 days.


Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[macropod]

Hmm,

If you've got the Analysis Toolpack installed, you could use the following:
=(INT(B1)-INT(A1))*24-(NETWORKDAYS(INT(A1),INT(B1))-1)*9
This has the advantage of also allowing you to specify holidays and have them added to the non-work hours also.

The formula in my previous post was an attempt to mimick networkdays without using the Analysis Toolpack. Given more time, which I don't have right now, I'm sure I could nail it.

Cheers

 

[bluedragon2]

Just for grins macropod, I created this formula a while back for someone who did not want to use the networkdays formula for some reason:

Start date is A1
End Date is B1
Create a List of Holiday Dates in two columns Like:
1/1/03 1
1/21/03 2

Then name that array Holidays.

Now put in:

=(B1-A1-(7-WEEKDAY(A1))-WEEKDAY(B1))-((B1-A1-(7-WEEKDAY(A1))-WEEKDAY
(B1))/7)*2+(IF(WEEKDAY(A1)=7,0,(6-WEEKDAY(A1))))+(IF(WEEKDAY(B1)
=7,5,WEEKDAY(B1)-1))-(VLOOKUP(B1,holiday,2,TRUE)-VLOOKUP
(A1,holiday,2,TRUE))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[mickyjune26]

Yeah, that fixed it.

Could I bother you to explain the last method posted while using networkdays?

Why are INT, *24, -1, and *9 used?

Thanks,
Micky

Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[mickyjune26]

That Q is meant for macropod. I took too long to post my reply.

Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[macropod]

Hi,

1. *24 to convert # days to 24 hours.
2. -1 to reduce networkdays count by 1. Networkdays does a count of days including the start & end dates, rather than counting the days between.
3. *9 to convert net # workdays to 9 available work hours.

Cheers

 

[mickyjune26]

Working great!

Micky Fokken
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.