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

 

[Steven1985]

Can anyone help on this at all?

Thanks

 

[Matt Rogers]

Hi Steven.

My suggestion:

The date is in D1
D1 is colored in 3 years

Formula:
=today()>=date(year(D1)+3,month(D1),day(D1))

Control: Change the date in D1 minus 3 years

Regards

Excel Workbook
	D
1	10.04.11
2	10.04.08
Sheet

<table border="0" cellpadding="0" cellspacing="0" width="438"><tr style="height:14.4pt" height="19"></tr><tr style="height:14.4pt" height="19"><td style="height:14.4pt" height="19">
</td></tr><tr style="height:14.4pt" height="19"><td style="height:14.4pt" height="19">
</td></tr><tr style="height:14.4pt" height="19"><td style="height:14.4pt" height="19">
</td></tr><tr style="height:14.4pt" height="19"><td style="height:14.4pt" height="19">
</td></tr><tr style="height:14.4pt" height="19"><td style="height:14.4pt" height="19">
</td></tr></table>

 

[Matt Rogers]

Sorry - Sorry - Sorry

please delete my last reply
wrong topic

 

[Steven1985]

Can anyone help?