FOI: Nights in Temporary Accommodation (TA)

This article explains how to calculate the total number of nights spent in temporary accommodation for the most recent financial year, including year-on-year totals and percentage change. Follow the steps below to extract and prepare the data in Excel. 

TABLE OF CONTENTS

• How to Generate the Nights in TA Data
• Add the Nights Calculation Formulas
• Format and Summarise the Data

How to Generate the Nights in TA Data

This section walks you through creating the TA Tenancy report in Locata and preparing the dataset in Excel.

1. Create the TA Tenancy Report

Use the following filters when generating the report:

Filter by Property

• Type of temporary accommodation → just display

Filter by Tenancy

• TA Tenancy Start Date → does have a value

• TA Tenancy End Date → just display

• Temporary Accommodation Type → just display

2. Export and Prepare the Data in Excel

Once the report is created, follow the steps below:

1. Generate the report.

2. Select all data: Ctrl + A, then Ctrl + C to copy.

3. Open Excel.

4. Paste the data using Ctrl + Shift + V.

5. Remove any rows above the header row.

6. Press Ctrl + A to select all.

7. Press Ctrl + T to convert to a table.

8. Click OK.

9. Autofit all columns: press Alt, then H, O, I in sequence.

10. Insert two new columns.

11. Name them:

    • No of Nights 23–24

    • No of Nights 24–25

Add the Nights Calculation Formulas

This section helps you calculate how many nights fall within each financial year window.

1. Insert the Financial-Year Formulas

In the No of Nights 23–24 column, enter:

=MAX(0,MIN(F2,DATE(2024,3,31))-MAX(E2,DATE(2023,4,1)))

In the No of Nights 24–25 column, enter: 

=MAX(0,MIN(F2,DATE(2025,3,31))-MAX(E2,DATE(2024,4,1)))

Formula Explanation

• MAX(E2, window_start) selects the actual start of the overlap.

• MIN(F2, window_end) selects the actual end of the overlap.

• If there is no overlap, it avoids negative numbers by returning 0. 

Example

A tenancy running 15/03/24 to 10/04/24 overlaps the 2024–25 financial year window (01/04/24–30/03/25) by 9 days, so the formula returns 9.

Format and Summarise the Data

This section covers how to finalise the calculation and build a pivot table summary.

1. Format the Nights Columns

1. Highlight both No of Nights columns.

2. Press Ctrl + 1.

3. Select Number under the Number tab.

4. Change decimal places to 0.

5. Click OK.

2. Create the Pivot Table

1. Click Insert on the Excel Menu Bar.

2. Select PivotTable.

3. Choose New Worksheet.

4. Click OK.

5. Add the following to the pivot table.

6. In the Temporary Accommodation Type filter, tick (blank) only.