# Rollbase: How do I calculate the number of days between an &

Posted by RustyPipe on 21-Jan-2016 13:54

How do I convert an entered date value to days since 1/1/1900 as a formula.

Scientific accuracy is not required

(I don't need to worry about leap seconds, milliseconds GMT, Server time, Time zones or Julian days in this instance) + or - 2 days will do..

i.e. difference between 1st Jan 1900 and 1st Jan 1970 = 25567 days.

The reason I ask this is, I am trying to calculate the number of days between an entered date and 1/1/1900.

Thanks

#### All Replies

Posted by jsniemi79 on 21-Jan-2016 14:56

Using your 25567 days, the code below will convert that to milliseconds, add it to the milliseconds since 1/1/70 and then convert the total to days, using normal rounding.  It comes up with 42389 after rounding. There might be an easier way to do this, but this should get you what you are looking for if I understand your issue.

var d = new Date();

var n = d.getTime();

var daysTo1900 = 25567;

var msBetween00And70 = 25567 * 24 * 60 * 60000;

var combinedMs = n + msBetween00And70;

var convertToDays = combinedMs / 60000 / 60 / 24;

var roundedDays = Math.round(convertToDays);

return roundedDays;

Posted by RustyPipe on 21-Jan-2016 17:17

Not sure I have explained this very well but thanks for the reply.

I am trying to calculate the number of days between an entered date and 1/1/1900.

i.e. 1/1/1900 is day one....all others are subsequent...I want to enter any date and for it to calculate the number of day in between.  I choose 1/1/70 as an example.

i.e.  It could be that the entered date is the  2/1/1900 which would be 2 days

or   the entered value may be the 4/1/1900 and the result would be 4 days

In Rollbase i.e. javascript, I have a field named "Your_Date" and a formula field named "days_since_1stJan1900"

Many thanks.

Posted by Thierry Ciot on 21-Jan-2016 22:10

Just use Javascript to compute the number of days between 2 dates:  here are some suggestions stackoverflow.com/.../how-to-calculate-the-number-of-days-between-two-dates-using-javascript

Posted by RustyPipe on 22-Jan-2016 01:03

Wow what a can of worms i have opened.

Will go through this very carefully.

Thanxs

Posted by RustyPipe on 22-Jan-2016 03:12

So I take it I should use date and time field (as in 1/1/1900 09:00 format )

and make that {!First_date}

then take the second date as {!Second_date}

turn the dates in milliseconds from today

take one from the other then convert back to seconds then minutes then hours then days?

as in 1000*60*60*24...

How does that sound?

I am lost...

Posted by Siddeshwar on 22-Jan-2016 06:42

Hello RustyPipe,

can you try below code, I tried it and got the answer 25567 days

var from = new Date("1/1/1900");

var to = new Date("1/1/1970");

var timeDiff = Math.abs(from.getTime() - to.getTime());

var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));

Thanks,

Siddeshwar.

Posted by jsniemi79 on 22-Jan-2016 08:00

Rusty,

If you take Siddeshwar's code above, just change the "1/1/1970" value in the "to variable to {!Your_Date} to use the field your user can enter.  That should always give you the difference between their date and 1/1/1900.  Then you won't need your other field for the days since 1970.

var from = new Date("1/1/1900");

var to = new Date("{!Your_Date}");

var timeDiff = Math.abs(from.getTime() - to.getTime());

var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));

Posted by RustyPipe on 22-Jan-2016 15:14

OK will try.

Posted by RustyPipe on 22-Jan-2016 18:04

Ok I created a new field known {!Your_Date} Return type date/time

It Validates but does not return an answer?

Posted by Siddeshwar on 25-Jan-2016 00:22

Hello Rusty,

The above code is working fine, follow these steps:

1)Create a 'Date' Field with name "mydate".

2)Create a 'Integer' Field with name "myintfield"

3)now keep this below code in any trigger with some timing option:

var from = new Date("1/1/1900");

var to = new Date("{!mydate}");

var timeDiff = Math.abs(from.getTime() - to.getTime());

var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));

rbv_api.setFieldValue("dateobj", "{!id}", "myintfield",diffDays);

4)Create record with some date.

5)you can see the resultant difference value is assigned to "myintfield" field.

Thanks,

Siddeshwar.

Posted by RustyPipe on 26-Jan-2016 11:36

Siddeshwar.

Many thanks This works now

var from = new Date("1/1/1900");

var to = new Date("{!mydate}");

var timeDiff = Math.abs(from.getTime() - to.getTime());

var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));

rbv_api.setFieldValue("dateobj", "{!id}", "myintfield",diffDays);

Once I removed the "{!id}" it validated and ran.  (I guess you had put that in as a database id field and normal good practise).

I also learnt about triggers, very useful.

Once again

Many thanks

I will study the above code carefully and take it a part and see why it works (that is how I learn).

Can I also say thanks to the wider community for the help and support, for a newbie. it was a positive experience (not one I expected) as I normally approach such forums with extreme caution and trepidation .

Rusty