Forum Discussion

XavierFan's avatar
XavierFan
Qrew Cadet
6 years ago

Re: Date in Number Format

Not sure what you mean by "number formatted date"?  Do you mean the DATEVALUE function in Excel?

This is not the same thing - but you could find out the number of seconds since a certain starting time, and use that number.

In this example Formula Numeric - the "start" is 1/1/1970, and the "end" is the current time, and the it returns the # of seconds.  

You can change the StartDateTime and EndDateTime variables in the first two lines to adjust the dates.

var DateTime EndDateTime = Now();
var DateTime StartDateTime = ToTimestamp(Date(1970, 1, 1));

var Number NumSeconds = ToSeconds( $EndDateTime - $StartDateTime );

$NumSeconds​

​

------------------------------
Xavier Fan
Quick Base Solution Provider
http://xavierfanconsulting.com/
------------------------------

5 Replies

  • 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
    ------------------------------
    • hhersch's avatar
      hhersch
      Quickbase Staff
      6 years ago
      Hey Adam - we would definitely suggest keeping our native key fields wherever possible both for functionality and performance. You mention wanting to handle import with updates, have you tried our "Merge Field" feature we rolled out earlier this year? Is there a feature gap with that?

      ------------------------------
      Harrison Hersch
      ------------------------------
      • AdamKeever1's avatar
        AdamKeever1
        Qrew Commander
        6 years ago
        I was not aware of the "Merge Field" feature. I will test it out, thanks Harrison.

        I use the "Merge Field" feature (guess I was so used to using it that I forgot I was using it).

        For this particular case I would need to merge based on four fields to ensure a unique ID. That would be a nice enhancement feature; add a "Merge Fields" feature that allows the user to select a number of fields in a specific order and create an ID field by concatenating the selected merge fields.

        ------------------------------
        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
        ------------------------------