Date format US not UK in Excel reports.

[Val4449]

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?

[Tina11799]

Hi,
I note your comments about having my own excel sheet with bookings etc on it, which I already do, as the AirBnB data is always a pain to manipulate.

My main point is regarding the Date formats in the CSV. When I download and enter into Excel you get columns with mixed formats in the same column. If it were just US format that is easy to fix to either leave it or convert to UK if needed. When you expand the column widths you get right & left justified "Dates". The Right justified dates are correctly in US Date format, however the Left justified Dates are actually in General format. I also tried changing the UK to US Language but makes no difference. See screenshots.

 

I have worked out a mini method to change General into USDate but why is this even happening? All dates are surely coming from the same source so why is the AirBnB system doing this? I have been importing CSVs from various sources into Excel for years and have never come across this happening. I can't think that my import is doing anything either as its just a 1 click operation.

Thoughts?

[Mike-And-Jane0]

@Tina11799 as @Joan2709 suggests you should keep your own spreadsheet with bookings, revenues etc and also costs. This is very handy when you come to fill out either the Inland Revenue tax return or the Valuation Office Agency business rates form both of which demand slightly different data even though they are part of the same government department.

[Tina11799]

You are correct & I do this as per my earlier reply. My main point is regarding the data formatting itself, which for a company as large as Airbnb should not happen.

Generally they don't really look outside of the US which is fairly typical of US companies unfortunately. (& I work for one in the UK & see this regularly). Airbnb have previously sent me instructions how to fill in US tax forms, even though I was on the phone from the UK.

[Joan2709]

Hi @Tina11799 

Can you share how you were able to change General format to make this work for you?

"I have worked out a mini method to change General into USDate ..."

Not sure this will work if the CSV file has jumbled date formats, but Excel allows you to choose a date format that automatically reverts dates to the operating system chosen country. Looks like this might have been tried without success though?:

[Tina11799]

Hi, Unfortunately that doesn't work as many of the "dates" are not in Date format but General.

Due to the different formats I have to read in each number of the date text string using =Left(A2, 2), MID(A2,4,2) & RIGHT(A2, 4). I assume the bad dates are in column A & start in row 2.

This gets the 3 parts of the US date i.e. 10/25/2023 you get 3 numbers 10, 25 & 2023. Do this into 3 additional separate columns, to keep it simple. 

Then in a 4th column formatted as DATE put in the date formula DATE(D2, C2, B2). This pulls in the 3 numbers you have just got in the 3 columns. Obviously switching the number order to UK date if needed. Also format the date layout as you need. I usually use the format 25 Oct 2023 to avoid issues figuring it out if it's UK or US date format. 

Drag the formulae down to the whole column. 

You then have a correctly formatted column in whatever date format you decided you need. 

Unfortunately you then have to do this for every date column you need adjusted. But cut&paste from the 1st column makes it quicker. 

I hope this helps