1 2 Previous Next 24 Replies Latest reply on Jan 10, 2018 1:45 PM by Jagjit Singh

# Calculate No of Days between Teams within Month

Hi All,

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

Thanks

Jag

• ###### 1. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

[Days with Repairs]

ZN(IIF(DATETRUNC('month',[Accepted by Allocations])<DATETRUNC('month',[Month_Start]),0,

DATEDIFF('day',

MAX([Month_Start],[Void Start Date]),

MIN([Month_End],[Accepted by Allocations]))+1))

[Days with Allocations]

ZN(IIF(DATETRUNC('month',[Accepted by Allocations])>DATETRUNC('month',[Month_Start]),0,

DATEDIFF('day',MAX([Month_Start]-1,[Accepted by Allocations]),MIN([Month_End],[Let Date]))))

Regards.

2 of 2 people found this helpful
• ###### 2. Re: Calculate No of Days between Teams within Month

Hi Jagjit,

Please find the workbook attached.

Also screenshot of results and formulas used.

• ###### 3. Re: Calculate No of Days between Teams within Month

Hi Lei,

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.

Thanks

Jag

• ###### 4. Re: Calculate No of Days between Teams within Month

Hi Khaild,

I have revised the datset and also attached the desired view.

Thanks

Jag

• ###### 5. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

I could not find "Referred to Assets" in the data, so I supposed it should be "Accepted by Assets".

Not very sure I've understood the logic well.

Anyway, please refer to the attached workbook to see if it meets your requirements.

Regards

• ###### 6. Re: Calculate No of Days between Teams within Month

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()

Thanks

Jag

• ###### 7. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Calculation modified in the workbook, along with a typo.

By the way, will there be a possibility that [Accepted by Assets] would be later than [Property Month End]?

If so, further modification is necessary.

Regards.

• ###### 8. Re: Calculate No of Days between Teams within Month

Hi Lei,

[Accepted by Assets] will always be later than previous [Property Month End date]. For example key were Accepted by Assets on 07/07/2016 which is later than 30/06/2016 but less than 31/07/2016

Thanks

Jag

• ###### 9. Re: Calculate No of Days between Teams within Month

Hello Jagjet,

Got it.

Please have a look at the workbook attached in my previous post to see if there's any incorrect calculation.

Regards.

• ###### 10. Re: Calculate No of Days between Teams within Month

Hi Lei,

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

Regards,

Jag

• ###### 11. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Done.

Regards.

• ###### 12. Re: Calculate No of Days between Teams within Month

Hi Lei,

Mostly errors with [Days Keys in locker] as highlighted below. I had a change in [Days with Assets] and have attached the revised workbook

Regards,

Jag

• ###### 13. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Do you require the 1s to be 0?

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.

Regards

• ###### 14. Re: Calculate No of Days between Teams within Month

In the above case the results are correct. We include both the days i.e [Referred to Allocations] and [Accepted by Allocations]

In the case of Prop Code 1367, the keys were referred to allocations on 11/03/2014, that's one day and accepted by allocations on 12/03/2014 - So means the keys were in locker for 2 days.

But in case the [Referred to Allocations] and [Accepted by Allocations] have same date then [Days Keys in Locker] = 0

Thanks

Jag

1 2 Previous Next