Date format US not UK in Excel reports.

Val4449
Level 2
Wellesbourne, United Kingdom

Date format US not UK in Excel reports.

The dates in the Excel CSV reports are in US format. Please can we have an option to have them displayed in other formats? If this is not possible can someone provide a way of of converting the dates from US to UK please?

19 Replies 19
Mike-And-Jane0
Top Contributor
England, United Kingdom

@Val4449 If you format the date cell in excel to a 'long format date' it spells out the months

Val4449
Level 2
Wellesbourne, United Kingdom

Thanks, I'll give that a try later.  ʘ‿ʘ

 

In the meantime I did find a way of using "Data-Text to columns" process which seems to work. It would be nice if the dates were already in UK format though or an option to request non-US date format. ¯\_(⌣̯̀⌣́)_/¯

 

Cathie19
Level 10
Darwin, Australia

I hear you @Val4449 & @Mike-And-Jane0 . We receive the same cumbersome and very annoying American date format as well; even though the Australian and New Zealand hosts use the same UK date format! You would think with the CSV excel software capability, there would be options… @Airbnb  @Quincy ?? 

Val4449
Level 2
Wellesbourne, United Kingdom

@Cathie19 I can see why they output a CSV file, but it would  be nice if date fields could be output in DD/MM/YYYY if requested.

 

Is there any chance of someone putting it forward, please? @Airbnb @Quincy

Bhumika
Community Manager
Community Manager
Toronto, Canada

Hi @Val4449 @Cathie19 ! Thank you for tagging Quincy! Just to double check, do you import your exported CSV files to google sheets or Microsoft Excel? 

Adding to @Mike-And-Jane0 's recommendation below, in Microsoft Excel, there's this option to arrange the dates as below. There's also a similar work around for google sheets I believe. Please let me know in case it helps.🌻

However, I'll be sure to share this feedback with the tech teams as well and invite you to share your feedback directly with Airbnb through this form : Feedback Form . 

Bhumika_0-1712336317951.png

-----

 

Please follow the Community Guidelines

Val4449
Level 2
Wellesbourne, United Kingdom

Hi @Bhumika

 

Thank you for joining @Cathie19 @Mike-And-Jane0 in the discussion and your suggestion.

 

In answer to your question I am opening the file using Excel Prof+ 2013.

 

My first observation of your picture is that the dates in column A look different to the ones in my CSV file. Your dates appear to be in the format of DD-MM-YYYY. Whereas the dates in my file are MM/DD/YYYY.

 

Unfortunately using the Format Date as you have displayed won't work.

 

 

Val4449 scr01.png

Mike-And-Jane0
Top Contributor
England, United Kingdom

@Val4449 

IT WORKS - I have the  solution - If you can find the dropdown shown in @Bhumika 's post then all you do is change the language from English (USA) to English (England). At least when I do this I can change my other spreadsheets to an American format.

To find this menu just click on the number dropdown and then click on more number formats.

Val4449
Level 2
Wellesbourne, United Kingdom

@Mike-And-Jane0 

 

Are you using a CSV file as shown in my picture above?

 

I have found only one way to format the dates supplied in a CSV as UK dates. It is as follows:-

 

Select column

Alt-A - E   (Displays the "Text to Column Wizard" box)

Select Delimited button - Click Next

Clear all Delimiter boxes - Click Next

Select Date button - Select MDY(from drop-down box) - Click Finish

 

This works!    ⸌(ㆆ‿ㆆ)⸍  @Bhumika @Cathie19 

 

So I have found a solution, but no thanks to @Airbnb  ( ͡°Ĺ̯ ͡° )

 

My point is that we are continually subjected to Americanisms, we now get our language as English(UK)!  I could understand if all dates came in YYYYMMDD! That would be logical to facilitate sorting. Apologies for my rant.

Mike-And-Jane0
Top Contributor
England, United Kingdom

@Val4449 To be fair they are an American company!

Val4449
Level 2
Wellesbourne, United Kingdom

@Mike-And-Jane0  True, but they are a Global organisation.

 

I should point out that this is Val's other half Cliff 🙂 dealing with all this "techy" stuff. I would be interested to know if you are using a CSV file with dates as they are presented?

Mike-And-Jane0
Top Contributor
England, United Kingdom

@Val4449 I just use the dates in the US format. Having lived there in the past I can switch between the two formats in my head with relatively few mistakes. I also keep separate spreadsheets of all bookings, payments etc so I am not reliant on Airbnb data.  

Val4449
Level 2
Wellesbourne, United Kingdom

Hi All,

 

I received an email asking if my problem was solved! It didn't actually give me a link to to reply so I can't tell them "No, the problem isn't solved."

 

@Bhumika - The link you provided was useful thank you but I'm not sure whether my feedback will result in any progress.

 

I hope I've explained my issue well enough and I do appreciate the help from community members. 

 

@Airbnb @Cathie19 @Mike-And-Jane0 

Tina11799
Level 2
Torquay, United Kingdom

Its not that the dates in the whole column are in just US format (which would be easy to convert to UK) but they are mixed in format. Some dates in the column are in US Date format but some are in Text format or even some unknown format.

 

What process in producing the CSV does it take to fix this basic issue? Over 4 years looking at other Forum posts !

 

I'm importing as CSV into Excel

Hi @Tina11799 

I agree that importing the CSV files and then converting to Excel is a pain!  I have to spend alot of time manipulating the columns, rows and data to make it useable. Like @Mike-And-Jane0 , I suggest my Host clients download the CSV files and convert to Excel and just save them in case they're needed. Instead of spending alot of time manipulating the data, I suggest they create their own Excel spreadsheet and enter each reservation in that with all the detail in case it's needed. This way you're not spending a ton of time trying to edit the CSV file, but it's always available in case you need a backup.

 

If you are seeing a series of pound signs (#####) in a cell, this is because the column widths are too small to display the data. Enlarging the cell width will allow the data to be shown correctly:

Joan2709_0-1719763485973.png

 

You can also change the language option in Excel to English UK. Not sure if that will display the number format differently, but worth trying.