# calculating dates

#### dianek

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### dianek

##### New Member
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

##### MrExcel MVP

Code:
``````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

##### MrExcel MVP
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?

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

#### dianek

##### New Member

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

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

#### dianek

##### New Member

Just confirmed and the cell is blank

#### Scott Huish

##### MrExcel MVP
This was the result of my formula:
notresolved.xls
CDEFG
410/26/2006 9:19Not_Due_Yet10/23/2006 15:01
Sheet1

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

What is the result?

#### dianek

##### New Member
Sorry there must have still been space

Excellent. Just got it to work.

#### dianek

##### New Member
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.

Replies
8
Views
192
Replies
3
Views
230
Replies
20
Views
1K
Replies
23
Views
511
Replies
2
Views
90

1,136,878
Messages
5,678,302
Members
419,753
Latest member
Sallylwy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back