Import date without the first day of month

hdace

Things have been going well with our test site and we're going live in October. Just one more question, I hope:

When we export to Excel from PastPerfect, the date output is usually in xx/xx/xxxx format (and the output is fine), although there are exceptions, like a few might look like ca. 1960, and CA imports the date as "circa 1960", and that's fine too.

The problem is that museum assistants have logged many of the dates by the month and year only, hundreds of times, like this: 05/1955. CA imports that as "May 1 1955". Is there a way to amend our importer mapping worksheet so that, only when this is the input date format, the output is "May 1955"?

Thanks!

hdace

I can understand why no one replied: it's not a problem with CA. It's Excel that's adding the day of the month. When one exports from PastPerfect to Excel, Excel can't just accept a month and a year, it has to add a day too. As far as I can tell, there is no way to stop it from doing it.

So here is an Excel function that one can add to a new column that will get rid of it:
=IF(CELL("format",F2)="D3", TEXT(F2,"mm/yyyy"), IF(CELL("format",F2)="D4", TEXT(F2,"mm/dd/yyyy"), F2))

I hope someone finds this useful...

seth

Set the column to text and it'll work

hdace

seth
That doesn't work. For example, 04/18/1956 comes out as 20563, which is obviously not helpful. You have to use the function.