6M Trend

[Abhishek_8895]

HI team,

I have two dates as you see, closed and lease end date.

I want to calculate the date difference of the ticket getting closed and the extra days it took and then bucket it into 0-15, 15-30, 30-60 and >60 days,.
The issue I am facing is that, there are many negative but also a few positive days difference. So how do I calculate to ensure the negative value shows the extra days and fit into the bucket but the early closure shows the less days and fit into the bucket for accurate reporting.

attaching file for reference

 

[Monty]

Hello

Here's an example of how you can do it in Excel:

Assuming:
- Cell A1 contains the closed date
- Cell B1 contains the lease end date

You can use the following formula in another cell to calculate the date difference:

=IF(B1>A1, B1-A1, A1-B1)

This formula calculates the absolute difference between the two dates. Now, let's assume you have predefined time frames for your buckets:

- 0-15 days
- 15-30 days
- 30-60 days
- >60 days

You can use nested IF functions to categorize the results into these buckets. For example, assuming the calculated difference is in cell C1, you can use the following formula to categorize the difference:

=IF(C1<=15, "0-15 days", IF(C1<=30, "15-30 days", IF(C1<=60, "30-60 days", ">60 days")))

This formula checks the value in cell C1 and categorizes it based on the predefined time frames.

You can adjust the cell references and time frames as needed for your specific spreadsheet.

 

[p45cal]

Is it (Closed)-(Lease End Date)
or
(Lease End Date)-(Closed)

If the result, in one case, is negative 10 days (-10), which bucket should it be in?