Forum Discussion

AdamKeever1's avatar
AdamKeever1
Qrew Commander
6 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's avatar
AdamKeever1
Qrew Commander
6 years ago
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's avatar
EverettPatterso
Qrew Captain
6 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's avatar
    AdamKeever1
    Qrew Commander
    6 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
    ------------------------------