Sims Reporting - Date Format

[emmaburt]

I regularly run a number of behaviour reports from sims and pull them out into excel. However the column with the behaviour date is formatted as Text, I use a vlookup formula from this date to calculate the week that the incident happened in for a summary sheet, however without manually changing the date from text to date by going into each cell i can't make it work.

 

does anyone know of a way of ensuring the date comes out of sims in date format rather than text?

[Michael]

Why can't you highlight the whole column and format as date in Excel?

[emmaburt]

I do that but it still doesn't actually change the format of the content in the cells as my vlookup doesn't recognise it as a date.

 

The format is comes out of sims is eg. 03 November 2016

[neilenormal]

??could you add an additional column into Excel after the date one that comes from sims, then use a use a formula such as =datevalue(cell to left)

Then format it as a date, but as a datevelue you can calculate terms etc. Useful when you want to calculate "term of birth" from sims into excel

[Pink_Panda]

highlight the column in Excel, on the Data tab, click on the 'Text to columns' button, leave it selected at 'delimited', click through the steps and finish. You can then change the column to a date format of your choice.

[Michael]

Other times I've seen date issues with spreadsheets/SIMS is to change the system region to USA (mm/dd/yyyy), then change it back to UK (dd/mm/yyyy).

[emmaburt]

ok thanks for the tips, i'll see what i can do. just frustrating to have to faff with it after it comes out of sims

[JHLEHS]

highlight the column in Excel, on the Data tab, click on the 'Text to columns' button, leave it selected at 'delimited', click through the steps and finish. You can then change the column to a date format of your choice.

 

Second this, we perform the task above and it normally fixes the issue.

[matt40k]

Sounds you need to learn PowerQuery @emmaburt - or Get Data as they call in Office 2016.

[emmaburt]

ooh sounds interesting? going to have to google PowerQuery, is it a add on to excel then?

[matt40k]

Add-on in Excel 2013, built in Excel 2016. Same tool as whats in PowerBI. Guy you want to look for is Chris Webb - he speaks at lot of events around the UK and does training. Loads of resources online about it - and its a reusable skill that isn't limited to the education sector!