1

Topic: Import wizard

I have been sent a spreadsheet of records containing dates in two separate columns, "start date" and "end date". Some of these dates are clearly a date range, some apply to a particular year only (e.g start date 01/01/1943, end date 31/12/1943) and some are applicable to a particular day (e.g. start date 30/06/2003, end date 30/06/2003). How do I import these records via the Wizard - can I reformat the date entries somehow so that the Import Wizard will accept the dates?

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

2

Re: Import wizard

R6 requires just one date column in R6 vague date format. The simplest way to deal with this is to create a new column  and use an excel macro to populate it with the dates from the other columns separated by a dash ( eg 30/06/2003-30/06/2003 or 01/10/1943-31/12/1943 ). The  downside to this is that internally R6 will hold the vague date type as DD (meaning dates between the two dates). This is perfectly valid but can look a bit strange in the observation hierarchy.

The alternative is to do more complex formatting. Where the two dates are the same then only one needs to go into the new column - this will give a vague date type of 'D' . For the years you would need just the year (eg 1943 ), giving a vague date type of 'Y'

Mike Weideli
R6 Consortium

3

Re: Import wizard

In Excel, going from 2 columns (start and end) to one date column you can use this formula:

=IF(A1=B1,A1,TEXT(A1,"dd/mm/yyyy")&"-"&TEXT(B1,"dd/mm/yyyy"))

where A1 is your start date, and B1 is your end date. If the dates are the same, it will return the start date, but if they are different if will return the range.  This should work no problems if your start date and and end dates are "actual" dates (a single day and in date format in Excel), but if not or you have a mix of formats you might get some unexpected results so use with care!

4

Re: Import wizard

I've written a small piece of VBA to convert from the old style NBN downloads which include a start date and end date but it requires the date type code (e.g. DD, MM etc). I can dig it out if its useful.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

5

Re: Import wizard

MIke and Andy - many thanks for your help on this - results look like the ones I need. I tried originally using the CONCATENATE function but that didn't give me the results I wanted...................also thanks Charlie - I'd like a look, it might be useful.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

6 (edited by charliebarnes 07-05-2019 10:22:47)

Re: Import wizard

Attached should be self explanatory

Post's attachments

nbn2recdate.txt 1.24 kb, 4 downloads since 2019-05-07 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership