calculate # of days from two different times
 

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

--
Fia
Fiji Islands

calculate # of days from two different times
 

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="",B 2=""),"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" wrote in message
...
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...

--
Fia
Fiji Islands

calculate # of days from two different times
 

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" wrote in message
...
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="",B 2=""),"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" wrote in message
...
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...

--
Fia
Fiji Islands

calculate # of days from two different times
 

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


"MartinW" wrote:

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" wrote in message
...
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="",B 2=""),"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" wrote in message
...
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...

--
Fia
Fiji Islands

calculate # of days from two different times
 

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

"Fia" wrote:

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


"MartinW" wrote:

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" wrote in message
...
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="",B 2=""),"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" wrote in message
...
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...

--
Fia
Fiji Islands

calculate # of days from two different times
 

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



"MartinW" wrote in message
...
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

"Fia" wrote:

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


"MartinW" wrote:

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" wrote in message
...
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="",B 2=""),"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" wrote in message
...
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...

--
Fia
Fiji Islands