Date & Times

[Steven1985]

Hi,

I have a current formula (Below) thats countS the date and time in HH:MM from one cell (A3) to (AO3).

This has included NETWORKDAYS to exclude counting if a weekend of bankholiday. This data is in Controls A10:A47

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

The issue I have is -

Date Received (A3) 02/09/2011 09:00 Outcome Date (AO3) 03/09/2011 11:15

Currently the formula would bring back -1 Days 21:15

I need it to ignore all of the 02/09/2011 as it's a sunday (a non working day) and count from 9am Monday 03/09/2011 so i want it to read 0 Days 2:15??

Is this even possible?

Many Thanks

 

[barry houdini]

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

 

[Steven1985]

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

Thanks

 

[barry houdini]

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....

 

[Steven1985]

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

 

[Matt Rogers]

Hi Steven

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!

 

[barry houdini]

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

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?

 

[Steven1985]

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

 

[barry houdini]

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?

 

[Steven1985]

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.