search

datacarpentry / spreadsheet-ecology-lesson

Data Organization in Spreadsheets for Ecologists
https://datacarpentry.org/spreadsheet-ecology-lesson
Other
37 stars 141 forks source link

added note about text being formatted as dates #231

Closed tracykteal closed 6 years ago

tracykteal commented 6 years ago

Addresses https://github.com/datacarpentry/spreadsheet-ecology-lesson/issues/5, adding the information that spreadsheet programs can mis-interpret text as dates.

hoytpr commented 6 years ago

I think we may need something a little more definitive, like an example. I know we don't want to add time to these lessons, but when working with 50,000 rows, this can be easily missed. The easiest example is to simply type a gene name like "SEP1" into a cell. It's automatically converted to "01-Sep" and in the formula bar it will say "09/01/2018".

If you try to copy the "01-Sep" and paste it as text, you get "43344". So Excel permanently changes your gene name to a date if typed into a native cell. Your best bet is to "preformat" the cells/columns as "TEXT" before you paste. Instinctually one wants to change the cell-type of pasted, date-formatted "genename" to "TEXT" but that will just just convert the date to a number, stored as text. Gets ugly fast.

I could write up a short example for "further reading". That way the class wouldn't be slowed. This change looks okay for now. Until I get a new page together, this should probably be merged (LGTM).