Date in Number Format

Question

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?

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

xavierfan · Answer

Not sure what you mean by "number formatted date"?&nbsp; 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 #&nbsp;of seconds.&nbsp;&nbsp;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 FanQuick Base Solution Providerhttp://xavierfanconsulting.com/------------------------------

adamkeever1 · Answer

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

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

everettpatterso · Answer

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.&nbsp;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 &amp; $codetwo &amp; $codethree​

adamkeever1 · Answer

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

Forum Discussion

Date in Number Format

6 Replies