calculating dates

[dianek]

Hi All,

I am hoping someone canhelp me with this.

Column C holds the Resolved date/time = 02/10/2006 9:38 (can hold spaces if not yet resolved)
Column E holds the Escalation date/time = 10/10/2006 6:32 (always populated)
Column F will hold a value of Pass, Fail or Not_Resolved based on the formula below.
Column G holds the current date/time

I have the formula as follows which is working
=IF(C4<"", IF(C4<E4, "Pass", "Fail"), "Not_Resolved")

I want to take this one step further and somehow work out

1.if the E4(Escalation)>G4(now) and the C4="" display a value of "Not_Due_Yet"
if the E4(Escalation)<G4(now) and the C4="" display a value of "Not_Resolved"

Is there a way to do this?

Thanks
Di

 

[dianek]

All,

Sorry that post was incomplete

I have the formula as follows which is working
=IF(C4<"", IF(C4<E4, "Pass", "Fail"), "Not_Resolved")

I want to take this one step further and somehow work out

1.if the E4(Escalation)>G4(now) and the C4="" display a value of "Not_Due_Yet"
if the E4(Escalation) )<G4(now) and the C4="" display a value of "Not_Resolved"

Thanks

 

[Scott Huish]

Your post fixed:

I have the formula as follows which is working 

=IF(C4<"", IF(C4<E4, "Pass", "Fail"), "Not_Resolved")

I want to take this one step further and somehow work out 

1.if the E4(Escalation)>G4(now) and the C4="" display a value of "Not_Due_Yet"

if the E4(Escalation)<G4(now) and the C4="" display a value of "Not_Resolved"

Is there a way to do this?

 

[Scott Huish]

Why does C4 hold spaces if not resolved, why not just blank or if the result of a formula why not ""
First I would fix that.

Then, does this work for you?

=IF(C4="","Not_" & IF(E4>G4,"Due_Yet","Resolved"),IF(C4<E4,"Pass","Fail"))

 

[dianek]

Hi thanks for helping.

The reason C4 is blank is due to the call not being actioned to a resolved status. Once this ocurrs the date\time will be populated in that field.

I tried this formula and received Fail for
C4 = blank
E4 = 26/10/2006 9:19
F4 = Fail - this is the result
G4 = 23/10/2006 15:01

I was after a Not_Due result for this combination

Cheers
Di

 

[Scott Huish]

It will return Not_Due_Yet if you take the spaces out of C4, actually make it blank, nothing in it at all.

 

[dianek]

Just confirmed and the cell is blank

 

[Scott Huish]

This was the result of my formula:

notresolved.xls
	C	D	E	F	G
4			10/26/2006 9:19	Not_Due_Yet	10/23/2006 15:01
Sheet1

In a blank cell somewhere, type this:
=LEN(C4)

What is the result?

 

[dianek]

Sorry there must have still been space

Excellent. Just got it to work.

Thanks heaps for your time

 

[dianek]

I need to refine this so hopefully you will be able to help again please:

Column C holds the Resolved date/time = 02/10/2006 9:38 (can hold spaces if not yet resolved)
Column D holds the Closed date/time = 02/10/2006 12:45 (can hold spaces if not yet closed)
Column E holds the Due date/time = 10/10/2006 6:32 (always populated)
Column F will hold the results of either
Pass = resolved + closed < Due
Fail = resolved > Due
Fail = resolved + closed > Due
Resolved_Not_Closed = resolved < Due, but closed > due

If you can you it would be appreciated.