calculate # of days from two different times

[Fia]

If A1=Date Reported, B2=Date Resolved, B3=Resolution Period
What formular should I use to:
1) calculate the resolution period in days, excluding weekends and public
holidays.
2) display "pending" in the Resolution Period cell if the Date Resolved cell
does not have a time value
3) display "impossible" in the Resolution Period cell if the value in the
Date Resolved Cell is less than the value in the Date reported cell
4) display "invalid" in the Resolution Period cell if either the Date
Resolved Cell or the Date Reported cell has no values.

Please assist...

 

[MartinW]

Hi Fia,

I think this should do what you want. (watch out for unwanted spaces if this
formula wraps)
=IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B2=""),"Invalid",NETWORKDAYS(A1,B2))))

You will need to have the Analysis Tool Pack loaded otherwise
NETWORKDAYS will return a #NAME error.
Also look in the help file for how to add the public holidays
to the NETWORKDAYS function.

HTH
Martin

 

[MartinW]

Woops,

I got the order of the error checking back to front. This might work better.
=IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2))))

HTH
Martin

Hi Fia,

I think this should do what you want. (watch out for unwanted spaces if
this formula wraps)
=IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B2=""),"Invalid",NETWORKDAYS(A1,B2))))

You will need to have the Analysis Tool Pack loaded otherwise
NETWORKDAYS will return a #NAME error.
Also look in the help file for how to add the public holidays
to the NETWORKDAYS function.

HTH
Martin

 

[Fia]

Hi Martin,
Thank you for replying.
"Pending" should only be diplayed if the Date Resolved column is empty.
Ideally, when users log an error report in the table, the Resolution Period
column should display the following values for each scenario:

DateReported DateResolved ResolutionPeriod
23/06/08 25/06/08 2
23/06/08 Pending
23/06/08 20/06/08 Impossible
20/06/08 Invalid
Invalid

Thanks,
--
Fia
Fiji Islands

Woops,

I got the order of the error checking back to front. This might work better.
=IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2))))

HTH
Martin

 

[MartinW]

OK, so in your first post, in your second requirement, where you said 'time
value' you meant 'date value' and in your fourth requirement where you said
'either' you meant neither. Is that right?

If so then I think this should be what you are after
=IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending",IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2))))

NETWORKDAYS counts whole days and therefore it will count 23/06/08 to
25/06/08 as 3.
To get it to return 2 you just have to subtract 1 from the result, so the
formula will be
=IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending",IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)-1)))

Does that work any better?

Regards
Martin

 

[MartinW]

OK, now I'm back on my own computer I can see your example formatted
properly. Try this.

=IF(OR(A1="",AND(A1="",B2="")),"Invalid",IF(B2="","pending",IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)-1)))

HTH
Martin