calculating dates

dianek

New Member
Joined
Oct 22, 2006
Messages
12
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
Joined
Oct 22, 2006
Messages
12
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your post fixed:

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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 22, 2006
Messages
12

ADVERTISEMENT

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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 22, 2006
Messages
12

ADVERTISEMENT

Just confirmed and the cell is blank
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 22, 2006
Messages
12
Sorry there must have still been space :(

Excellent. Just got it to work.

Thanks heaps for your time :)
 

dianek

New Member
Joined
Oct 22, 2006
Messages
12
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top