Forum Discussion

Qrew Commander

5 years ago

Date in Number Format

I have a case in which in excel I convert several date fields to the number formatted date in order create a unique numeric key field. Any suggestions on how to do this in Quick Base? ----------...

Ask about Quickbase functionality

Tables and fields

AdamKeever1

Qrew Commander

Hello Xavier,

Yes, I am referring to the Microsoft Excel date-time code. Here is an example of how I am generating a unique ID that can be used as a key field:

I want to have the data in a table in Quick Base and set the code as the key field so that I can use the import with updates function to add records and update modified records.

------------------------------
Adam Keever
------------------------------

EverettPatterso

Qrew Captain

5 years ago

Can you elaborate more on what you wanting to achieve? If you already have the key on excel, what are you wanting to change?

Excel date-time is calculated based on the number of days since January 1, 1900.
Quick Base date-time is calculated in milliseconds since January 1, 1970.

You can convert it in a formula, but you can't set that formula numeric as a key field. So that's why I ask what you are wanting to change in your process.

//from January 1, 1900 to January 1, 1970 = 25,569 days
var number codeone = ToDays([Date1]-Date(1970, 1, 1)+Days(25569));
var number codetwo = ToDays([Date2]-Date(1970, 1, 1)+Days(25569));
var number codethree = ToDays([Date3]-Date(1970, 1, 1)+Days(25569));

$codeone & $codetwo & $codethree

AdamKeever1
Qrew Commander
5 years ago
Thanks for the input Everett. I was trying to avoid generating the key in excel each time I have data to import to Quick Base. Not a big deal to do it, I won't be importing very frequently for this case.

------------------------------
Adam Keever
------------------------------