Date & Times

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 29, 2011

• #2

What's possible here? Can the outcome date also be a weekend or a holiday....or will that always be a working day?

 

Upvote 0

S

Steven1985

Board Regular

Joined

Mar 2, 2011

Messages

66

• Sep 29, 2011

• #3

The outcome date will never be a weekend or a holiday. Just working days.

Thanks

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 29, 2011

• #4

Try this version

=IF(AO3="","",NETWORKDAYS(A3,AO3,Controls!A$10:A$47)-1-(NETWORKDAYS(A3,A3,Controls!A$10:A$47)*MOD(A3,1)>MOD(AO3,1))&" days "&TEXT(AO3-NETWORKDAYS(A3,A3,Controls!A$10:A$47)*A3,"h:mm"))

Note: there are two additional NETWORKDAYS functions in there - they deliberately both refer to A3 twice - that's just a way to test whether A3 is a holiday....

 

Upvote 0

S

Steven1985

Board Regular

Joined

Mar 2, 2011

Messages

66

• Sep 30, 2011

• #5

It almost works but I think there is a slight problem as the example below -

Column A Column AO

10/09/2011 09:00 14/09/2011 09:10

The formula used shows as 2 Days 9:10, but shouldnt it read 2 Days 0:10??

Thanks

 

Upvote 0

Matt Rogers

Well-known Member

Joined

Sep 23, 2011

Messages

1,030

• Sep 30, 2011

• #6

Hi Steven

Code:

The formula used shows as 2 Days 9:10, but shouldnt it read 2 Days 0:10??

I think it's 4 days & 10 minutes.

Excel Workbook
	A	B	C
1	10.09.2011 09:00	14.09.2011 09:10	4 Days 00:10
Sheet

Forget it, it's not Networkdays ...
SORRY!

 

Last edited: Sep 30, 2011

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 30, 2011

• #7

Steven1985 said:

.....and count from 9am Monday....

Click to expand...

OK, I missed this part from your first post - the formula I suggested just excludes Weekend and holiday hours and therefore starts counting from Midnight on Sunday in this case.

To start at 09:00 would seem odd given that your original formula is counting 24 hours a day, e.g. the original formula would count this

Mon 12/09/2011 04:00 > Mon 12/09/2011 10:00

as 0 Days 6:00

i.e. counting all the hours,

but this now should be.....

Sun 11/09/2011 04:00 > Mon 12/09/2011 10:00

would count as 0 Days 1:00

is the first one wrong - should that be 1 hour too?

I'm unclear now on which hours you want to count....is it continously from 09:00 on Monday to Midnight on Friday (or some other time on Friday?) or something else?

 

Upvote 0

S

Steven1985

Board Regular

Joined

Mar 2, 2011

Messages

66

• Sep 30, 2011

• #8

Hi Barry,

Yes, below is correct. We only need to count hours worked in the day to work an alert. As we start at 9am Monday and completed it by 10am this has only taken 1 hour. Even if this alert was presented to us Saturday 17:00, this should still show as 1 hour taken.

Sun 11/09/2011 04:00 > Mon 12/09/2011 10:00

would count as 0 Days 1:00

Thanks

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 30, 2011

• #9

OK but I'm trying to establish a general rule so that any formula I suggest will work for all circumstances; what should the hours be if "Received" was 23:00 on a Tuesday and "Outcome" was 10:00 the following day...are you counting all 11 hours there?

 

Upvote 0

S

Steven1985

Board Regular

Joined

Mar 2, 2011

Messages

66

• Sep 30, 2011

• #10

OK but I'm trying to establish a general rule so that any formula I suggest will work for all circumstances; what should the hours be if "Received" was 23:00 on a Tuesday and "Outcome" was 10:00 the following day...are you counting all 11 hours there?

I would ideally like to count buisness hours Only so if it cam it at 11pm Tuesday and the Outcome was 10am Wednesday id like to read 1 hour.

There 8 hours in the working day 9 - 5 so if it came it at 9am Tuesday and didnt have the outcome until Wed 10am Id like it to say 1 day 1 hour.

 

Upvote 0