Help with my if statements?

[islic1411]

Greetings MrExcel message board!

I appreciate you taking the time to read my question and thank you greatly for any insight you may have. I have inherited someone’s excel file that has been passed down from person to person over an unknown number of years. It is being used to create charts based on ticket information and has a data connection back into BMC @ our client’s office. I noticed at least 1 issue with poking around in the formulas and It’s just a hair outside my thinking ability today.

The "raw data" dump has calculated columns where we would calculate the duration a ticket has been opened and then group the count of the tickets into buckets. The user created several columns to parse out the data and I believe the problem is coming in with the red sections below

Incident Number	Status	Submit Date	Last Resolved Date	Age - Hours	Age - Days	Age - Days (Rounded)	Age Group
(normal information pulled in via the data connection)	(normal information pulled in via the data connection)	(normal information pulled in via the data connection)	(normal information pulled in via the data connection)	=([@[Last Resolved Date]]-[@[Submit Date]])/"1:0:0"	=([@[Last Resolved Date]]-[@[Submit Date]])/"24:0:0"	=ROUND((AI16),0) [ in this example AI16 is the “age days” column]	=IF(AJ15<3,"0-2",IF(AJ15<7,"3-6",IF(AJ15<15,"7-14",IF(AJ15<5000,"'>14")))) [In this example the AJ15 is the “Age – Days (Rounded)]
INC000087153112	Assigned	5/02/2016 6:33:25 PM	[ These have not been resolved so there is no date in this cell]	-1201457.124	-120.11	-120	0-2 [In this example the ticket should show up in the >14 days but because there is no resolve date, its doing 0-submit date so there is a negative number and that is less than 0 and assigning it to the wrong bucket [0-2]]
INC000087148476	Pending	5/02/2016 6:33:25 PM	[ These have not been resolved so there is no date in this cell]	-1201457.124	-120.11	-120	0-2 [In this example the ticket should show up in the >14 days but because there is no resolve date, its doing 0-submit date so there is a negative number and that is less than 0 and assigning it to the wrong bucket [0-2]]
INC000087145430	Resolved	5/12/2016 6:33:25 PM	5/13/2016 6:33:25 PM	24	1	1	0 - 2
INC000087144843	Closed	5/12/2016 6:33:25 PM	5/13/2016 6:33:25 PM	24	1	1	0 - 2

<tbody>
</tbody>

How can I fix the calculations to look first and see if there is a resolve date. If there is one then the formulas work perfectly but if there isn’t one then we need to use [today or hard coded date] – submit date?.... I tried a few items that if the numbers in the (age days or age hours) were below zero to ignore them and I was going to build a special column for those but I didn't have much luck with that either.

Please also, if there is an easier way share


Thank you guys!

 

[wesborland1234]

I think you should be able to preface that IF with another IF for when its negative.

=IF(AJ15<0,">14",IF(AJ15<3,"0-2",IF(AJ15<7,"3-6",IF(AJ15<15,"7-14",IF(AJ15<5000,"'>14")))))

 

[islic1411]

Thank you. I actually tried that but I put it at the end of my statement. There are instances when a ticket would have a submit date of a couple days ago and no resolve date. This will cause the rounded days to be negative however if you look at the submit date it is 3-4 days old (as an example) and not > 14.

Example: (Based on today's date this would show as 8 days old and not 14+

Incident Number	Status	Submit Date	Last Resolved Date	Age - Hours	Age - Days	Age - Days (Rounded)	Age Group
INC000087153112	Assigned	5/12/2016 17:09		-1020065.15	-42502.71	-42503.00	>14

<tbody>
</tbody>

Any other thoughts?