Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[marko]

This is not simple (for me at least), so I've attempted to simplify as much as possible. I've bumped into an issue with MC, Dates, and Math, as outlined below, which hopefully demonstrates my problem...

A: convertdate(15//01//1985 01:01) Returns: 31062.0423611111109494
B: convertdate(14//01//2023 14:32) Returns: 44940.6055555555576575
C: convertdate(22//01//2023 13:28) Returns: 44948.5611111111138598

math(C-(B-A))
math(44948.5611111111138598-(44940.6055555555576575-31062.0423611111109494)) Returns: 31069.997917

formatdate(31069.997917,datetime) Returns: 22/01/1985 23:57 (expected result: some time during 13th or 14th January 1985)
formatdate(31069.997917,elapsed) Returns: 38.02 years (expected result: 37.99 years)

Any time after 01:01 on the 15th January 2023 should then flip the elapsed result on to 38.00 years. In order for this to happen, I need to subtract 11 from "B"

If "A" were 14 November 1964, and "B" were 13 November 2022, I need to subtract 16 from "B" to correct the drift.

Can anyone tell me why?

Thanks

[zybex]

Leap years.

This MC date format represents the number of days elapsed since 1899/12/31 (or 1900/01/00), with the fractional part corresponding to the hours/minutes/seconds. It's the same format as Excel uses - you can put those numbers in a cell, format it as Date and it shows you the exact same dates.

When doing math with dates, context matters. Which dates? If you do 2022/01/01 - 2021/01/01, you get 365. But 2021/01/01 - 2020/01/01 is 366 because those dates cover a leap day. This is what causes your math to go progressively off - every 4 years it goes off by another day. MC functions don't actually take this into consideration, they just do the simple math between the numbers (as does Excel).

The formatdate(days, elapsed) actually ignores this and just divides the number of days by 365 (not 365.25), so for large ranges it becomes progressively off.

If you want to get a date relative to another date, you can't use these functions, or you need to complicate the math a bit to take into account the leap years. The solution depends on what exactly you're trying to do.

[marko]

Leap Years. DOH!  Thanks zybex.

The solution depends on what exactly you're trying to do.

A = A birth date
B = The date of a photo
C = Now()

removeright(formatdate(math(C-(B-A)),elapsed),9) will return the age of the person with birthdate "A" tagged in a photo, at the time said photo was taken.

I track the ages of 17 people and a cat in this way, and have done for some years now, but only recently noticed that photos taken just before birthdays were showing the wrong age. Auto import applies all 18 birth dates to every photo imported. After that, the simple act of tagging any of those people or the cat in any photo triggers the age calculation for each one.

My mother is in her eighties. That's a lot of leap years. Do you think it's possible to calculate around that?

My solution of simply subtracting from "B" is obviously not going to be reliable.

[Matt]

Would a CompareDates function be nice that takes two dates and returns something like 34.36 days or 7.41 years?

[Matt]

Although it might be tough with leap years as well, so I'm not sure.

[zybex]

Would a CompareDates function be nice that takes two dates and returns something like 34.36 days or 7.41 years?

Yes, otherwise getting accurate date deltas is extremely hard. Maybe DateDiff(date1, date2, mode) ?
mode = 0: default, automatic range formatting with label ("34.36 days" or "7.41 years")
mode = 1: number of years, no label ("7.41")
mode = 2: number of days, no label - similar to "math(trunc([date2]-[date1],2))"

Although it might be tough with leap years as well, so I'm not sure.

The integer part would always be the number of full years between the dates, and the fractional part could just be the remaining number of days divided by 366 (to prevent overflow). I think it's OK to assume always 366 as 1/366 is just 0.002, so the error is small.

[zybex]

This returns the correct age and elapsed years for any dates, but without decimals (the first 2 save() are for testing in Zelda). Adding decimals is a pain. Any range less than 1 year returns 0 years:

save(convertdate(22//01//1973),_birth)
save(convertdate(23//01//2003),_event)

Age of person at [_Event] date:
math(formatdate([_event],yyyy)-formatdate([_birth],yyyy)-if(compare(Formatdate([_event],MMdd),<,Formatdate([_birth],MMdd)),1,0)) years old

Years elapsed since [_Event]:
math(formatdate(math(now()-[_event]+1),yyyy)-1900) years ago

... so yes, a new function would be nice for this

Age: DateDiff([_birth], [_event]) old
When: DateDiff(now(), [_event]) ago

[marko]

That works. Thanks zybex, that'll be another pint I'm due you

I've kept my original portion of the expression that returns hours, days and months old for when they were babies, and dropped your solution in at the end for years. I've compared two thus far and all looks great. I'll update the fields at the weekend.

The ages are part of the image caption when a slideshow is playing and the girls really like it. Thankfully, they have not noticed that at times, they were getting older faster than they might have liked.

-marko

[Matt]

We're going to add an expression function next build.  Feedback welcome.

[zybex]

Just for kicks, here's what a full solution looks like in Expression Language  Thankfully Matt's function is arriving!

Code: [Select]

save(convertdate(23//01//1960 18:00),date1)/
save(convertdate(22//01//2023 15:00),date2)/

save(min([date1],[date2]),d1)/
save(max([date1],[date2]),d2)/
save(Math([d2]-[d1]), days)/

save(formatdate([d1],%Y),d1y)/
save(formatdate([d1],%m),d1m)/
save(formatdate([d1],%d),d1d)/
save(formatdate([d2],%Y),d2y)/
save(formatdate([d2],%m),d2m)/
save(formatdate([d2],%d),d2d)/
save(Math([d2y]-[d1y]),years)/

save(Math(frac([d1])+convertDate([d1d]//[d1m]//[d2y])),b1)/
save([b1],t1)/
if(compare([t1],>,[d2]),
  save(math([d2y]-1),d2y)/
  save(Math(frac([d1])+convertDate([d1d]//[d1m]//[d2y])),t1)/
  save(Math([years]-1),years)/
,
  save(math([d2y]+1),d2y)/
  save(Math(frac([d1])+convertDate([d1d]//[d1m]//[d2y])),b1)/
)/

save(math([b1]-[d2]), bDays)/
save(math([d2]-[t1]), yDays)/
save(Math([years]+[yDays]/366),years)/

===========================

Start Date    : formatdate([date1],datetime)
End Date      : formatdate([date2],datetime)

Total Years   : [Years]
Total Days    : [Days]
Last Birthday : [yDays] days ago
Next Birthday : [bDays] days

Formatted     : Math(int([years])) years + Math(trunc([yDays],2)) days


[marko]

We're going to add an expression function next build.  Feedback welcome.

Hi Matt, apologies for the delay. I have soo little time these days, it's madness.

Should CompareDates() be factoring in leap years? Because, it's not, or at least, appears not to be.

In the screen cap below,
The first column is my original system, which is wrong.
The second column is using the expression provided by zybex above, which is correct. For my usage here, "[_event]" = "Date Of Photo"

Code: [Select]

math(formatdate([_event],yyyy)-formatdate([_birth],yyyy)-if(compare(Formatdate([_event],MMdd),<,Formatdate([_birth],MMdd)),1,0)) years oldThe third column is using CompareDates(). Neither "auto" or "c" account for leap years, or, both are returning an incorrect result for another reason?

Thanks for looking at this, you know it's appreciated.

-marko

[Matt]

comparedates(ConvertDate(01//15//2023), ConvertDate(01//15//1985), y) returns -38.000 years in the next build.

[zybex]

Try mode "yd". There are still some rounding errors, but Matt is working on it.


Start Date    : 26/01/1960 10:00:00
End Date      : 25/01/2023 20:00:00

The hard way:
Total Years   : 62.995674
Total Days    : 23010.416666
Last Birthday : 364.416666 days ago
Next Birthday : 0.583334 days
Formatted     : 62 years, 364.41 days

Latest MC:
CompareDates(date1, date2, a): 63.04 years
CompareDates(date1, date2, c): 63 years
CompareDates(date1, date2, d): 23010.417 days
CompareDates(date1, date2, y): 63.001 years
CompareDates(date1, date2, yd): 62 years, 364 days

[marko]

All good now. Thank you, both. Hopefully I'll have some time during this weekend to review all of the [age] fields here.

-marko

[zybex]

This is a must-watch for programmers (and funny for non-programmers):
https://www.youtube.com/watch?v=-5wpm-gesOY

Date math is HARD!

[joshhuggins]

That is great reminder zybex, thanks for the share!

[DangerJP]

Hi,

I've just tested the new ComparDates() function to compute the [Age at death] of Thelonious Monk:

[Artist DOB] = 1917-10-10
[Aritst DOD] = 1982-02-17

[Age at death] = CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), c) = 65 years

So, I understand that the 'c' mode rounds to the nearest integer.

In my specific case, it would be nice to add a mode that rounds to the lower integer, so it would compute the real age at death (64).

[zybex]

Mode 'c' is just 1982-1917= 65 calendar years; it doesn't take the day/month into consideration.

Mode 'y' is what you want:
CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y) = 64.356
Math(trunc(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y), 0)) = 64

[DangerJP]

Mode 'c' is just 1982-1917= calendar 65 years; it doesn't take the day/month into consideration.

Mode 'y' is what you want:
CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y) = 64.356
Math(trunc(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y), 0)) = 64

Thanks zybex!

But on my setup, the results are:
CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y) = 64,356 years
Math(trunc(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y), 0)) = 0

Maybe its because of my system using the ',' as the decimal separator instead of the '.' ?

[zybex]

The dot vs comma issue was present in the past, but I think that was fixed.

The issue is probably because CompareDates() returns "years" as a text label, causing trunc() to fail. Try:
Math(trunc(Number(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y)), 0))
or
Regex(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y),/(\d+/),1)

[DangerJP]

The dot vs comma issue was present in the past, but I think that was fixed.

The issue is probably because CompareDates() returns "years" as a text label, causing trunc() to fail. Try:
Math(trunc(Number(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y)), 0))
or
Regex(CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y),/(\d+/),1)

Awesome!

That Number() function (which I wasn't aware of BTW) did the trick.

Thanks again for your precious insight, much appreciated!

jp

[Striker]

CompareDates(ConvertDate([Artist DOB]), ConvertDate([Artist DOD]), y) = 64,356 years

Very Nice...

If someone would be so kind... what expression would I use to show me the number of years since the track was released?

I use the Year field which is a 4 digit year as YYYY which is the release year.

Basically I'd want CurrentYear - ReleaseYear.  I would want an int result, not a float.

Thanks in Advance.

[DangerJP]

Very Nice...

If someone would be so kind... what expression would I use to show me the number of years since the track was released?

I use the Year field which is a 4 digit year as YYYY which is the release year.

Basically I'd want CurrentYear - ReleaseYear.  I would want an int result, not a float.

Thanks in Advance.

Hi,

This works for me:

Math(trunc(Number(CompareDates(ConvertDate([Year]), ConvertDate(FormatDate(Now(), yyyy)), y)), 0))

[zybex]

Or
Math(FormatDate(now(),%Y)-[year])

[DangerJP]

Or
Math(FormatDate(now(),%Y)-[year])

 
Lol, so much cleaner and simpler!

BTW, I guess a simple Math() is faster than CompareDates()?

I'm thinking of using this new function in calculated fields.

[Striker]

Or
Math(FormatDate(now(),%Y)-[year])

Thank You Sir.  Works great.

This is what I used

if(isempty([year]), ,Math(FormatDate(now(),%Y)-[year])y)

[lepa]

The dot vs comma issue was present in the past, but I think that was fixed.

It still exists. I think there was an attempt for a fix but it was reverted as that fix broke expression parsing somewhere else. or something like that

[lepa]

BTW is there easier way to calculate minutes elapsed?

I have view which shows songs played during last 2 hours in minutes but expression is kind of cumbersome. It would be nice if "Elapsed" could be formatted also

Math(FirstNotEmpty(ListItem(FormatDate(ListItem([Play History], -1), Elapsed),-3,:),0) * 60 + ListItem(FormatDate(ListItem([Play History], -1), Elapsed),-2,:)) min ago

Or same with stock [Last Played]
Math(FirstNotEmpty(ListItem(FormatDate([Last Played, 0], Elapsed),-3,:),0) * 60 + ListItem(FormatDate([Last Played, 0], Elapsed),-2,:)) min ago

[zybex]

Dates in MC increase by 1 per day. The fractional part is then 24 hours, or 1440 minutes, or 86400 seconds. So you just need:
Math(int((now()-[Last Played,0])*1440)) minutes ago

These will print nice time intervals for any range, from minutes to years (what "elapsed" should actually do?):

Code: [Select]

if(regex(save(FormatDate([Last Played,0], Elapsed),_timeago,1),/#(?:(\d+):)?(\d+):(\d+)#/,0),Math(0[R1])h [R2]m ago,[_timeago] ago)
or

Code: [Select]

if(regex(save(FormatDate([Last Played,0], Elapsed),_timeago,1),/#(?:(\d+):)?(\d+):(\d+)#/,0),Math(0[R1]*60+[R2]) minutes ago,[_timeago] ago)

[lepa]

Dates in MC increase by 1 per day. The fractional part is then 24 hours, or 1440 minutes, or 86400 seconds. So you just need:
Math(int((now()-[Last Played,0])*1440)) minutes ago

These will print nice time intervals for any range, from minutes to years (what "elapsed" should actually do?):

Code: [Select]

if(regex(save(FormatDate([Last Played,0], Elapsed),_timeago,1),/#(?:(\d+):)?(\d+):(\d+)#/,0),Math(0[R1])h [R2]m ago,[_timeago] ago)
or

Code: [Select]

if(regex(save(FormatDate([Last Played,0], Elapsed),_timeago,1),/#(?:(\d+):)?(\d+):(\d+)#/,0),Math(0[R1]*60+[R2]) minutes ago,[_timeago] ago)

Thanks! I guess I was too tired to start figuring out how fraction part calcultes although now it is so obvious  I'll though need those darn replaces 
Math(int((replace(now()-[Last Played,0],/,,.))*1440))

Will save the regexes as they sure will be handy in some projects

[lepa]

regex in action  Added now playing track to first spot with current last played info.

<Offtopic>
Only problem with the view is that I don't remember if there is auto refresh option per view or at all. In general I wouldn't want MC to auto refresh views I'm in but for this view it would be nice last touch. I can press F5 of course but it would be nice to have playing now playlist independent log which shows progress without manual trigger.