Aging Days for 2 date ranges

[Simone2527]

I need to be able to have aging days on a report i have for cases. The Aging days need to have an If statement to have the aging days be the # between 2 date ranges summed. So a case is assigned 5/24/18. We send a request for more information on 6/20/18. Days lapse from that is 27. The requested info is sent in on 7/5/18. The case is completed on 7/13/18. Time lapse from that is 8 days. So there is a total of 35 days that should show up on the aging column. So i can do a simple function to get 35 days by subtracting out each date range for the lapse days and then adding the lapse days together. What i'm having trouble with is when we do not have 4 dates and i still need to have the lapse time show in aging. I have a picture of the spreadsheet below. Any help would be appreciated.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date Assigned[/TD]
[TD]Date Completed[/TD]
[TD]Aging[/TD]
[TD]Info Request[/TD]
[TD]Info Received[/TD]
[/TR]
[TR]
[TD]5/24/18[/TD]
[TD]7/13/18[/TD]
[TD]35[/TD]
[TD]6/20/18[/TD]
[TD]7/5/18[/TD]
[/TR]
[TR]
[TD]5/31/18[/TD]
[TD][/TD]
[TD]?[/TD]
[TD]6/21/18[/TD]
[TD]7/6/18[/TD]
[/TR]
[TR]
[TD]6/28/18[/TD]
[TD][/TD]
[TD]?[/TD]
[TD]7/5/18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

Welcome to Mr. Excel

I can think of 2 possible ways

1. You can use if statement to show "Zero" in the aging column in case the date completed is blank
Formula like : =If(B2="",0,B2-A2)

2. If you don't need to see the aging at each stage & just the overall aging, then you could do Max date - Min Date

Formula like : =Max(A2,B2,D2,E2)-Min(A2,B2,D2,E2)​

 

[Simone2527]

Welcome to Mr. Excel

I can think of 2 possible ways

1. You can use if statement to show "Zero" in the aging column in case the date completed is blank
Formula like : =If(B2="",0,B2-A2)

2. If you don't need to see the aging at each stage & just the overall aging, then you could do Max date - Min Date

Formula like : =Max(A2,B2,D2,E2)-Min(A2,B2,D2,E2)​

Thank you for this but i need both the ability to see the current aging date and the final aging date. Every time i try to right out my If Statement it always errors out. So i don't believe i'm doing it correctly.

 

[mse330]

Hey Simon,

Check the below example & let me know if that suits your need

Book1
	A	B	C	D	E	F	G
1	Date Assigned	Info Request	Aging 1	Info Received	Date Completed	Aging 2	Overall Aging
2	24-May-18	20-Jun-18	27	05-Jul-18	13-Jul-18	8	35
3	31-May-18	21-Jun-18	21	06-Jul-18		0	21
4	28-Jun-18	05-Jul-18	7			0	7
Sheet1

Cell Formulas
Range	Formula
F2	=IF(E2="",0,E2-D2)
F3	=IF(E3="",0,E3-D3)
F4	=IF(E4="",0,E4-D4)
G2	=F2+C2
G3	=F3+C3
G4	=F4+C4
C2	=IF(B2="",0,B2-A2)
C3	=IF(B3="",0,B3-A3)
C4	=IF(B4="",0,B4-A4)