AIRBNB Exporting CSV file dates in mixed format? How to make DD/MM/YYYY ?

Paul-and-Dawn0
Level 2
Northern Ireland, United Kingdom

AIRBNB Exporting CSV file dates in mixed format? How to make DD/MM/YYYY ?

I have exported the years booking history and the file is totally unworkable as the date format is mixed between multiple formats. I want to unify the date to DD/MM/YYYY so that I can file the correct years revenue with the tax authorities. 
Why does Airbnb not provide a simple file in this regard and does anyone know how to fix this issue? 

7 Replies 7

How can i get the booking history file

Ah, i see monthly report now, but it's at a summary level. Is there any way to get more detailed data?

Paul-and-Dawn0
Level 2
Northern Ireland, United Kingdom

The file provides everything, booking date, payment date, reservation number, payouts, gross booking value, cleaning fee etc 

 

the issue is that the date section is mixed format and can’t easily be sorted or changed to DD/MM/YY. 

Mike-And-Jane0
Top Contributor
England, United Kingdom

@Paul-and-Dawn0 just reformat the date column and all will be well. Excel dates are really just a number that can be formatted in any fashion you want. Its all very clever and allows dates to be subtracted from each other (amongst other things)

Paul-and-Dawn0
Level 2
Northern Ireland, United Kingdom

I know how to do that, but if you Google this issue I’m not the only one having issues. It won’t reformat the entire list, it might work on half the number.. 

 

I found a pretty long work around by going to text by column, selecting the dates and delimiting, then putting the range into the DDMMYY. 

thanks for replying though, much appreciated. 

Lorna170
Level 10
Swannanoa, NC

@Paul-and-Dawn0   If you are working with the CSV converted to Excel, you should be able to fix the dates.

 

In Excel:  Highlight the date column you want to work with.  On the Home Ribbon, Number Section, select the dropdown arrow in the format box at the top of the Number section.  Then go to the very bottom of the dropdown menu for More Numer Formats.  Click.

 

A Format Cells box opens.  On the Number tab, Category Box, select Date.  Ignore the Sample and type option boxes as the CSV is using the short date format.  Instead, go to the Locale (location) dropdown, and select English (United States) or English (United Kingdom).   All of the dates should then convert to the format you have selected.

 

Good luck!

Paul-and-Dawn0
Level 2
Northern Ireland, United Kingdom

Thanks for the reply, I’ll try that next time but I managed to resolve it by talking a longer approach using text to column and selecting delimiting  and adjusting to DDMMYY. It wasn’t just a simple reformat of the date. 

my language settings are English so I’m not sure why it’s doing this.