search

datacarpentry / spreadsheets-socialsci

Data Organization in Spreadsheets for Social Scientists
http://datacarpentry.github.io/spreadsheets-socialsci/
Other
21 stars 68 forks source link

Safi_dates.xlsx date format issue #108

Open elletjies opened 4 years ago

elletjies commented 4 years ago

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 commented 4 years ago

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

elletjies commented 4 years ago

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 commented 4 years ago

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

elletjies commented 4 years ago

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

chris-prener commented 4 years ago

Great, will do the same @elletjies !

elletjies commented 4 years ago

@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 commented 4 years ago

OK - will check into this tomorrow @elletjies!

elletjies commented 4 years ago

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

allie-tatarian commented 7 months ago

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.