I have the attached sample data and would like calculate the no of days a property is with repairs and no of days its with the allocations team within the month. We are basically calculating the days based on the key movement, i.e when the keys were with assets and allocations. When there is a value in the let date field this means the property is no longer vacant.

Logic:

Days with Assets = Accepted by Assets – Referred to Allocations ( If there is a date value for accepted by assets and no value for referred to allocations, we still need to calculate days with assets using End of Month Date or Today().

During this period Days with Allocations = 0, if the Referred to Allocations and Accepted by Allocation values are NULL)

Days with Allocations = Accepted by Allocations – Let Date (During this period Days with Assets = 0. In case the keys were referred to allocations in between the month, the calculation should show the days keys were with assets and allocations within the month. So if the keys were referred to allocations on 5th of the month, the property was with assets for 5 days ie from 1st to 5th. Now we calculate the no of days from accepted by allocations to let date for days with allocations )

Days Keys in locker – Referred to Allocations – Accepted by Allocation ( This is the time when the keys are passed from assets to allocation)

If Referred to Assets and Accepted by Allocations are both Null, then Days with Assets and Days with Allocations is 0

Sorry I forgot to take into consideration two fields and have uploaded a sample data again. I have applied your formula to the new dataset and if you can review the calculations please. I have update the logic on the original post.

Sorry that's a typo and you are correct its accepted by assets. Also I found an error with one of the dates so please use the attached workbook. I have refreshed the original data source as well. I have manually calculated the days and highlighted the once which are wrong in the attached spreadsheet.

Another scenario is that when keys have been accepted by assets but there is no value for referred to allocations we still need to calculate days with assets accepted by assets - month end or today()

I guess this will be the last scenario. If [Referred to Assets] is NULL or prior to the [void start date] then days with assets will be calculated from Void Start Date to Referred to Allocations. Please use the attached dataset as I have included this scenario

I noticed in the sheet "Required_Result" of the first Excel you posted (which has been updated) that, the day difference is one day more than what the formula datediff(...) gives, so I add +1 to the calculation.

Thus,

Prop Code

Property Month End

Month Start

Month End

Void Start Date

Accepted by Assets

Referred to Allocations

Accepted by Allocations

Let Date

3. Days Keys in Locker

1367

2014/03/31

2014/03/01

2014/03/31

2014/02/13

2014/02/13

2014/03/11

2014/03/12

2014/04/15

2

3653

2014/06/30

2014/06/01

2014/06/30

2014/06/11

2014/06/10

2014/06/17

2014/06/20

2014/06/24

4

So, with the same logic, when "Referred to Allocations" = "Accepted by Allocations", the result is 1.