Safi_dates.xlsx date format issue

[elletjies]

While preparing the first tab (DD_MM_YY) interview_date variable does not allow for extraction of year, date or month.

The MM_DD_YEAR tab's interview_date variable is in the format of YY-MM-DD and this allows for extraction of year, date or month

[chris-prener]

Hi @elletjies - so it sounds like that tab needs to be moved from YY-MM-DD to MM-DD-YY, correct?

[elletjies]

In the lesson it asks you to choose as per your countries convention, for example South Africa will choose DD-MM-YY and US MM-DD-YY. The problem is the format of the first tab is really funny and I cant extract the year/month or date here (=year or =month or =day)

[chris-prener]

ok gotcha - will have to take a closer look. if you have suggestions for making it workable, let me know!

[elletjies]

@chris-prener will think how we can perhaps use the other datasets (already used in this lesson) to achieve the same outcome

[chris-prener]

Great, will do the same @elletjies !

[elletjies]

@chris-prener I edited the SAFI_dates file and it seems to be working now (well on my Excel). I did the following:

1. Updated the tab names so it is a correct reflection on what is going on in the data

2. Edited the data of the MM-DD-YY file so that year, month and day can be extracted.
   SAFI_dates.xlsx

Shout if it is not working on your side.

[chris-prener]

OK - will check into this tomorrow @elletjies!

[elletjies]

@chris-prener just checking in - did the changes to the file work?

[allie-tatarian]

I know this issue is pretty old, but I ran into the same thing (or a very similar issue) when I taught this lesson last week. I tested the lesson using the MM-DD-YY tab, and it worked fine, but many of my students wanted to use the DD-MM-YY tab, and it didn't work. The dates in that tab are not formatted as dates, they are formatted as text and Excel can't see them as dates (at least the North American version of Excel). I think this could be fixed by:

1. In a North American version of Excel, input the dates in MM/DD/YYYY format (or copying the MM-DD-YY tab)
2. Select column A, right click, go to Format Cells...
3. On the Number tab, change the locale dropdown to "English (United Kingdom)" or another country that uses the DD-MM-YY format

When I make those changes on the MM-DD-YY tab, where the first column is formatted as numbers, it looks the same as the DD-MM-YY tab, but the date/month/year extractions actually work.