[sims] DOB exporting as text to Excel

[StevieM]

Our school secretary was trying to export a report to Excel. In the preview, the DOB field was in DD/MM/YYYY format, but it in Excel it was DD Month YY. She couldn't change the format, as it was exported as text not a number.

 

If the report is exported as RTF, there is an explicit option to export dates as numbers. Any ideas how to do this for Excel?

[neilenormal]

IIRC you have to add an extra column, with a formula something like "=date(b2)" [where cell b2 has the sims exported date in it] and then format that any way Excel can.

Its a pain!

[StevieM]

It's =datevalue(b2), and the cell formatting needs to be set to date. It was easier to just export it to word in .rtf format and just copy and paste the table. It's ridiculous there is no similar option for Excel.

 

Thanks for the pointer, though. I didn't know about the date/datevalue functions. I'm sure they'll come in handy in the future.

[datamanbs]

Once it has exported to excel, select the column and format it to date dd/mm/yyyy. Keep the column selected and do a find and replace of space to space. This will edit every row as all the dates have a space in them. As soon as it edits them, it realised that the date should now be dd/mm/yyyy and does this for you.