Hi,
I have to properties in my sleeping log notes:
Now I want to create a property column with an user function, which doesn’t show anything, if the difference is 24 hours (default value of the template) and shows the actual duration, if I changed the default value to the correct dates.
if((note[“sleeping until”]-note[“sleeping from”])== 24 * 60 * 60 * 1000,“”,note[“sleeping until”]-note[“sleeping from”])
=> doesn’t work..
As far as I’ve tested this and assuming both of your keys are of type date & time 
, it seems like:
note["sleeping until"] - note["sleeping from"] returns a duration so I don’t think you can compare it directly to a number (24 * 60 * 60 * 1000)…
You can coerce the date/time keys into numbers though, using number() which you should then be able to compare to 24 * 60 * 60 * 1000
So, this seems to work for me
if(number(note["sleep until"]) - number(note["sleep from"]) == 86400000, "", note["sleep until"] - note["sleep from"])
Thanks, that helped a lot!!! 
One further question:
(I use my original formula with german properties (sleeping from = Schlafen von; sleeping until = Schlafen bis)
This is now my formula:
if(number(note["Schlafen bis"]) - number(note["Schlafen von"]) == 24*60*60*1000, "", note["Schlafen bis"] - note["Schlafen von"])
which produces this result:
=> the results > 0,5 hours are rounded…
How can I change the output format to somewhat like “2:30 h”?
I also didn’t manage to calculate the duration in ours, with the following formula:
if(number(note["Schlafen bis"]) - number(note["Schlafen von"]) == 24*60*60*1000, "", number(note["Schlafen bis"] - note["Schlafen von"])/1000/3600)
About the comparison, bases provides the function duration() to parse a string into a duration, so you can:
pduration: Schlafen bis - Schlafen von.if (formula.pduration == duration("24h"), "", formula.pduration)
I’ve got to say that playing around with durations can be hard 
…
I’m not sure you can really get something like 2:30 h (as there isn’t, AFAIK, a way to format durations) but you could have 2,5 h though
if(number(note["Schlafen bis"]) - number(note["Schlafen von"]) == 24*60*60*1000, "", ((number(note["Schlafen bis"]) - number(note["Schlafen von"])) / (60 * 60 * 1000)).round(1) + " h")
I know the “otherwise” part of the if() is not great looking with all the brackets …
(Without them, the math was just not working)
For some reasons, that didn’t work for me 
…
It worked with >= or <= but not ==
Probably because of “rounding errors”. I mean, perhaps the difference between the two dates is not exactly 24 hours. One millisecond of difference is enough to render == false.
I got awfully curious ( 
) about displaying the resulting difference between the 2 date/times formatted as h:mm and I got something …
The math isn’t necessarily pretty to look at so I divided the logic into multiple formulas …
The 1st step is to determine the difference between dt2 and dt1 as a whole, in ms which is done using the formula (in the column titled diff (ms)):
number(dt2) - number(dt1)
The 2nd step (column mm (ms)) is to determine the amount of minutes (in ms) that would be “left” if one was to express the previously calculated difference in hours ().
This can be done using the modulo / remainder operator (%):
formula["diff (ms)"] % (60 * 60 * 1000)
The 3rd step (column hh (ms)) would now be to determine the amount of hours (still in ms) within the “whole duration”… which can be achieved by subtracting the previously calculated amount of minutes from the duration:
formula["diff (ms)"] - formula["mm (ms)"]
Then comes the conversion of each “part” of the duration into the appropriate units…
This is done, in the column titled h (from ms) with:
formula["hh (ms)"] / (60 * 60 * 1000)
… and in the column m (from ms) with:
formula["mm (ms)"] / (60 * 1000)
All that’s left is to put everything together (column titled diff (h:mm from ms)):
[
formula["h (from ms)"],
if(formula["m (from ms)"].toString().length == 2, formula["m (from ms)"],"0" + formula["m (from ms)"])
].join(":")
… where the if() is there to preprend the amount of minutes with a zero if necessary …
The whole thing can be simplified a little relying on seconds instead of milliseconds though .
In that case, the 1st formula (diff (s)) becomes:
(number(dt2) - number(dt1)) / 1000
The 2nd one (mm (s)):
formula["diff (s)"] % (60 * 60)
The 3rd one (hh (s)):
formula["diff (s)"] - formula["mm (s)"]
Then for the conversions: h (from s):
formula["hh (s)"] / (60 * 60)
… and m (from s):
formula["mm (s)"] / 60
Putting it all together in diff (hh:mm from s):
[
if(formula["h (from s)"].toString().length == 2,formula["h (from s)"],"0" + formula["h (from s)"]),
if(formula["m (from s)"].toString().length == 2,formula["m (from s)"],"0" + formula["m (from s)"])
].join(":")
Note: This assumes that dt2 is always greater than or equal to dt1 and that both date/times don’t go further than the minutes 
(meaning that in Source mode, both date/times have their seconds set to 00).
Still with the same constraint where dt2 needs to be >= dt1 (otherwise that won’t work ), the logic can be applied to date/times with a precision of seconds this time …
Like previously, the 1st step is to get the duration between the 2 date/times and I chose to express it in seconds (this time ) in the column diff (s):
(number(dt2) - number(dt1)) / 1000
The 2nd step (in ss (s)) would be to determine how many seconds would be left if the previously calculated difference/duration was to be expressed in minutes …
formula["diff (s)"] % 60
The 3rd step (in mm (s)) would be to know how many seconds would be left if the difference/duration was to be expressed in hours, minus the previously calculated amount of seconds (
)…
formula["diff (s)"] % (60 * 60) - formula["ss (s)"]
Then, in hh (s), the amount of seconds for the hours can be calculated using:
formula["diff (s)"] - formula["mm (s)"] - formula["ss (s)"]
After that comes the various conversions …
In h (from s):
formula["hh (s)"] / (60 * 60)
… and in m (from s):
formula["mm (s)"] / 60
There’s no conversion for the seconds as they were already calculated …
Last but not least, putting it all together in diff (hh:mm:ss from s):
[
if(formula["h (from s)"].toString().length == 2,formula["h (from s)"],"0" + formula["h (from s)"]),
if(formula["m (from s)"].toString().length == 2,formula["m (from s)"],"0" + formula["m (from s)"]),
if(formula["ss (s)"].toString().length == 2,formula["ss (s)"],"0" + formula["ss (s)"])
].join(":")
Note (2) : I tested this completely out of curiosity/fun and on a very very small sample with date/time values written in Source as YYYY-MM-DDTHH:mm:ss nothing more (meaning, I can’t necessarily guarantee the results in any other cases )
Thank you very much, I learned a lot out of your post!!! … yeah, I know this kind of curiosity… 
… dangerous!
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.