You may want to know how far in advance guests are booking a stay at your property. Here is a tutorial on how to add the number of days guests book before staying at your property on the booking summary report.
Split the booking date and booking time
- Insert a new column to the right of the Booking Date column.
In this example, it's column W.
Video guidance on how to add or remove a row or column in Excel. - Select and highlight the filled cells of the Booking Date column.
- Click on the 'Data' tab.
- Choose the 'text to columns' option.
- Click 'Next' on the pop-up.
- Tick the 'Space' box
- Click 'Next'.
- Click 'Finish'.
Your booking date and time should now be in separate columns.
Calculate the lead time
- Highlight booking date column, and right-click to open the menu.
- Select 'Format cells...'. Video guidance on how to format numbers in cells on Microsoft Excel
- Select the 'Date' category.
- Select the format 'dd/mm/yyyy' format
e.g. 14/03/2012 in the example on Excel. - Click 'OK'.
- Insert new column to the right of column B (This will be the column for the lead time data and can be added where you prefer on the sheet).
Video guidance on how to add or remove a row or column in Excel. - In cell C4 (of the newly inserted column C), add the formula for:
=(your Arrival Date column)-(your Booking Date column)
In this example this is =B4-W4.
After adding your formula:
- Set the cell formatting to zero decimal places - we're counting whole days for our lead time.
Video guidance on how to format numbers in cells on Microsoft Excel - Copy formula down to bottom of report using the drag tool on Excel.
Video guidance on how to copy a formula down a column or row in Excel
You should then have the lead time, in days, for all bookings on the report. It provides insight into how many days in advance a guest books to stay at your property.
