Dealing with Dates (It can get complicated!)

*Apologies if you arrived here after doing a search on how to deal with dates in your love life, however we are talking calendar dates here unfortunately! 😉

Hi all, thought I would do a really quick post on what can be a simple topic but lead to very complicated issues: Dates! Why would something like dates require it’s own blog I hear you ask? These days it’s not unusual for a sports organisation and the sport science/S&C department in particular to work with a variety of softwares on a daily basis. Often these can include softwares from around the world, and there is no guarantee the data export, date in particular, will be formatted how we want it. This means it can be useful to know both how to fix this and how to prevent it causing more issues later on in our analysis

There are three basic date formats we come across on a regularly which we will call European (DD/MM/YYYY), American ( MM/DD/YYYY) and Asian (YYYY/MM/DD). If you only work in Microsoft based applications then this will be a quick fix for you. In general Microsoft applications will check the region selected for your PC/laptop and apply that region’s date format as the default for all applications. This means if you come across data with a different date format its a really quick fix by:

• Import into Excel
• Select the relevant cells or columns
• Right click
• Change format
  • Either select Date or use a custom format if the desired one isn’t present (As discussed previously this can be automated in PowerQuery). 
  • If you are changing the date to the canonical format in Excel you may have to change it as a custom format by typing YYYY-mm-dd into the available space.

• 

• 

However, if you work across multiple platforms, (Excel, R, Python, Tableau etc) then dates can cause a real headache. Some softwares will read all date format, others will get confused with some date formats. However there is a solution at hand: the ‘Canonical Date’ . This is YYYY-MM-DD (e.g. 2018-09-25) and yes having the – instead of the / is important. This is the one date format that will (hopefully) never cause issues. All softwares recognise this date format meaning you will never have to worry about messing around with complicated formulas to change a date format. If you work across multiple platforms, I would suggest changing to this date format early in your analysis and then changing to your preferred format at the end of your analysis.

And that’s my quick tip for working with dates!! 🙂