How 2 Insert Sequence of Dates

[gadfly]

I'm importing some data from a spreadsheet that includes a column of dates that were not formated as dates. It's a simple table containing some daily class attendance numbers for a single year. I've got a total of 365 records and they are sorted correctly so all I need to do is insert the dates into the date field. Is there some kind of simple calculation I can use to replace the contents of the date field with a sequence of dates starting January 1, 2005 and ending Decmber 31, 2005?

TIA

Edited April 24, 2006 by Guest

[LaRetta]

Yes, but do you want just a sequence Jan - December? Or do you want to convert the text-date listed in each record to a real date? What is the format of the text date data? Is this a one-time process or ongoing?

Either way it can be done - I just need a bit of clarity. :wink2:

[gadfly]

The date field doesn't actually hold dates, just text like this: jan1, jan2, jan3, etc. Therefore, I thought it would be best to simply wipe out the existing data and somehow plug in real dates in the proper sequence. I was thinking that since it's possible to insert a sequence of serial numbers there ought to be a way to insert a sequence of dates, which are really just numbers, aren't they? I just don't have the experience working with date calculations to figure it out.

I hope that makes it more clear. Thanks.

[LaRetta]

Yes, thanks, that helps. But I don't know if this is a one-time thing or ongoing need. If you want auto-enter serials, create a serial field (number) called numbDate. And in the Auto-Enter, click 'Serial Increment' and put this value:

731947

That is 1/1/2005. Each subsequent record will take on that number (plus 1) thus incrementing the date. I see you also want weekends listed so this will work. If you don't want weekends, let us know and we can skip them in the serializing. If you do this through import, don't import the text date at all. This serial will become your date. But this process should CREATE the records during the import - Add as New records and 'perform auto-entry'! You can't Update Existing records because you have no match key.

For displaying the date, just have a date field ready (with Auto-Enter, Do Not Replace). The auto-enter calc will simply be: numbDate. But I would be concerned if you got out of import sequence. It would all break. And you wouldn't be able to create a new record with a different date. You can also just use Records > Replace Field Contents to increment this serial 731947 by one instead (and click 'update auto-enter options). In this case import without the date then manually run the serial process through them.

Without knowing more about your needs, I can't be more specific. But I hope I've given you ideas. Always back up your file before trying something new. :wink2:

[gadfly]

That was exactly what I needed. I was trying more complex calculations and your solution was so simple I couldn't see it. Thanks very much.