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..
The many answers I have read seem varied and confusing.
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
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;
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.
Many thanks for the reply.
Wow what a can of worms i have opened.
Will go through this very carefully.
Thanxs
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...
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.
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));
OK will try.
Ok I created a new field known {!Your_Date} Return type date/time
Cut and pasted your code.
It Validates but does not return an answer?
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.
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