topic Re: Calculate Birthdays Anniversary / Jubilee from birthday date. in ArcGIS Survey123 Questions

Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Fri, 16 Dec 2022 11:37:22 GMT

Hello every one,

my question is quite straightforward: I want to enter a birthday date and have the Birthday jubilee, f.i. the 18th, the 20th, 30th birthdays and so on , automatically calculated.

I used the following formula (from here Decimal date time—ArcGIS Survey123 | Documentation😞

18th Anniversary: date(decimal-date-time(${Geb_Datum}) + (365.25*18))
20th Anniversary: date(decimal-date-time(${Geb_Datum}) + (365.25*20))

But when I enter a Birthday, the calculation seems to have a 1-Day error, but only in some Jubilees.

Birthday date 01.12.2020

Very strange is that depending on the date entered, the same calculation seems to be right:

Birthday date 01.11.1979

PLEASE HELP!!😕

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

MichelleWilliamsERM — Fri, 16 Dec 2022 17:25:26 GMT

Great question! I'm wondering if it's a leap-year issue.

I found this online; let me know if this fixes your calculations.

Leap years — years with an extra day at the end of February — were invented as a way to keep calendar years (which are normally 365 days long) and astronomical years (which are 365.2425 days long) in sync with one another.

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Mon, 19 Dec 2022 10:47:34 GMT

Hello Michelle,

thank You for your suggestion, I tried it and guess what? the results were inverted. The Birthday date 01.12.2020 previosuly wrong, was now right. And the birthday date 01.11.1979 previously right is now wrong.

Any other ideas?

Thank You very much!!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Mon, 19 Dec 2022 10:48:33 GMT

Hello guys,

@IsmaelChivite I read all your BLOGS and didn´t come across such a problem. Any suggestions please??

@JamesTedrick You are en expert in Survey123 Solutions, have you ever came acrosso such a case?

I very much appreciate your help.

Thanks in advance and my kindest regards,

Dorella

THANKS!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

AlfredBaldenweck — Mon, 19 Dec 2022 15:54:43 GMT

It was a problem with the leap year.

(I am not 100% sure on how exactly this works, but here's my thought process to solve this)

Specifically, if the year input is a leap year, you have to account for the difference by subtracting one day.

if(format-date(${Ex8},'%Y') mod 4 = 0, date(decimal-date-time(${Ex8}) + ((365.25*18)- 1)), date(decimal-date-time(${Ex8}) + ((365.25*18))))

Formula explanation: if the input year is evenly divisible by 4, subtract one day for a leap year, otherwise add as normal.

This yields the following for December 1, 2020, and for November 1, 1979, as

You'll notice that for leap years, the 20th and 40th anniversaries are showing up incorrectly. That is (as best I can tell), because those years are evenly divisible by four also.

To fix this, just use your original formula for those fields.

date(decimal-date-time(${Geb_Datum}) + (365.25*20))

To summarize, the following should help you:

Type	Name	Label	Calculation
date	Ex8	Example 8
date	Ann18Fix	18th Anniversary Fixed	if(format-date(${Ex8},'%Y') mod 4 = 0, date(decimal-date-time(${Ex8}) + ((365.2518)- 1)), date(decimal-date-time(${Ex8}) + ((365.2518))))
date	Ann20Fix	20th Anniversary Fixed	date(decimal-date-time(${Ex8}) + (365.25*20))
date	Ann30Fix	30th Anniversary Fixed	if(format-date(${Ex8},'%Y') mod 4 = 0, date(decimal-date-time(${Ex8}) + ((365.2530)- 1)), date(decimal-date-time(${Ex8}) + ((365.2530))))
date	Ann40Fix	40th Anniversary Fixed	date(decimal-date-time(${Ex8}) + (365.25*40))

Hope this helps!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Tue, 20 Dec 2022 14:52:22 GMT

Dear Alfred,

I thank You so much for your suggestion. I will be trying this during this week and let you know how it worked. I am also sure it has to do with the leap year, only I find it SO STRANGE that ArcGIS didn´t even consider/mention such a problem. I mean if you read the description on the Blog of Mr. Chivite about Dates and Time, leap years are not even mentioned....

Thanks you again, I will come back to You as soon as i tested it out.

Kindest regards

😊

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Thu, 22 Dec 2022 11:58:24 GMT

Dear Alfred,

I tried your suggested solution but unfortunately it didn't work, or at least something in the logic needs to be adjusted.
When I enter my first two TEST birthday dates (01/01/2020 and 11/01/1979) your solution adapts perfectly, but when I randomly choose another birthday date it sometimes doesn't work.

Please look:

I hereby also attach the xls form screenshot:

I think there must be a way to correct this, perhaps by converting the result to a string and then applying logic that if the number representing the DAY is different than the number in the "Birthday" field, should then be replaced back into a date field, but that's way too complex for me. And anyway, I still can't believe Esri hasn't come across such a problem yet. Maybe not for anniversaries, but for example to calculate when an inspection needs to be repeated in the future or when an object needs to be changed or checked in the future... I'm not sure I can explain myself clearly enough, I apologize for that.

Thank you for your help and I look forward to your opinion.

Best regards!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

AlfredBaldenweck — Thu, 22 Dec 2022 16:52:57 GMT

Hi DoZ,

That is really strange.

I took another look, and it looks like it's specifically having issues with the year AFTER a leap year, as well.

Luckily, that's an easy fix.

if(format-date(${Ex8},'%Y') mod 4 = 0 or format-date(${Ex8},'%Y') mod 4 = 1 , date(decimal-date-time(${Ex8}) + ((365.25*18)- 1)), date(decimal-date-time(${Ex8}) + ((365.25*18))))

This formula checks to see if it's a leap year or the year after by seeing if it is evenly divisible by 4, or if it has a remainder of 1. (I checked each year from 1977 to 2010, so it should be good)

1977-1981 for proof:

Type	Name	Label	Calculation
date	Ex8	Example 8
date	Ann18Fix	18th Anniversary Fixed	if(format-date(${Ex8},'%Y') mod 4 = 0 or format-date(${Ex8},'%Y') mod 4 = 1 , date(decimal-date-time(${Ex8}) + ((365.2518)- 1)), date(decimal-date-time(${Ex8}) + ((365.2518))))
date	Ann20Fix	20th Anniversary Fixed	date(decimal-date-time(${Ex8}) + (365.25*20))
date	Ann30Fix	30th Anniversary Fixed	if(format-date(${Ex8},'%Y') mod 4 = 0 or format-date(${Ex8},'%Y') mod 4 = 1 , date(decimal-date-time(${Ex8}) + ((365.2530)- 1)), date(decimal-date-time(${Ex8}) + ((365.2530))))
date	Ann40Fix	40th Anniversary Fixed	date(decimal-date-time(${Ex8}) + (365.25*40))

Like you say, it's strange that leap years aren't accounted for.

Hopefully this helps!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Fri, 23 Dec 2022 09:17:36 GMT

Hi Alfred,

I tried it out but please take a look at the Birthday date 1st of November for the 18th Anniversary with the formula:

if(format-date(${Geb_Datum},'%Y') mod 4 = 0 or format-date(${Geb_Datum},'%Y') mod 4 = 1, date(decimal-date-time(${Geb_Datum}) + ((365.25*18)- 1)), date(decimal-date-time(${Geb_Datum}) + ((365.25*18))))

This formula worked good for years: 1987, 1988,1989, 1990, 1991 till 2022 showing the 1st of November,

but for the years 1986, 1985, 1984, 1983, 1982, 1981 is showing 31st of October

It seems unfortunally there are no leap years involved in this logic...

Leap years were: 1980 1984 1988 1992 1996

Then it get´s more interesting because for year 1980 going back to 1958 it is showing again the correct 1st of November BUT from year 1957 backwards is showing again the wrong date of 31st of October and also the 50th anniversary show a different date..

I am getting so upset! I can´t seem to find a logic... 😥

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Fri, 23 Dec 2022 11:57:58 GMT

Hi Alfred,

it´s me again! So I tried something different....

I converted the birthday date into an integer and added 18 to get the year in which the person will celebrating the 18th birthday.

After this I created a TEXT field where I take the Day and Month from the birthday´s field and add to it the calculated year as a string. Now I have the correct day and Month and the year in which the person will be 18 years old.

After this I just need to convert everything back into a date field:

I am aware of the fact that I will need 2 extra fields for each anniversary, but I intend to set those fields as hidden and with bind::esri:fieldType as null these will not be published to the feature Service.

What do you think? 😊 maybe is not neat...but at least i avoid the leap year problem.

Regards

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

AlfredBaldenweck — Fri, 23 Dec 2022 17:42:15 GMT

Wow, that is so strange.

I'm really sorry; my formula worked fine for me.

I think your solution should work! It's certainly a lot less confusing than mine.

That being said, I can't make it work on my end. No matter what date I input, the final date is always Wednesday, December 31st, 1969.

If it works for you, though, you should go for it! Just watch out for February 29th

As an aside, I was reviewing my formula and noticed it got messed up in the beginning of the year, as well as two years after the leap year. This formula should work for any date, assuming it works for you at all.

if(((format-date(${Ex8},'%Y') mod 4 = 0) and (format-date(${Ex8}, '%n') > 2)) or (format-date(${Ex8},'%Y') mod 4 = 1) or ((format-date(${Ex8},'%Y') mod 4 = 2) and (format-date(${Ex8}, '%n') <3)), date(decimal-date-time(${Ex8}) + ((365.25*30)- 1)), date(decimal-date-time(${Ex8}) + ((365.25*30))))

I tested it for February and March 1st of the years 2022-2027. It also works for February 29th; 18 or 30 years after the 29th is March 1st.

The gist of the formula is:

If a leap year AND after February
OR
If the year after a leap year
OR
Two years after a leap year AND after February

then subtract one day.

You are right; after muddling through this, I'm not sure why Esri hasn't figured out a solution directly.

Let us know how it works out!

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

MichelleWilliamsERM — Fri, 23 Dec 2022 17:53:31 GMT

@DoZ

Here are a few questions that might help you.

What version of Survey123 Connect are you using?
Are all your drivers up to date? I have a Dell, so I use Dell Command anytime things get wonky.
AND is Windows at a current version?

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Tue, 27 Dec 2022 09:02:39 GMT

Hello Michelle,

thanks for coming back to me, let see ifI can answer all your questions...

Survey123 Connect Version is the 3.13.249, is that ok?

I also have a Dell and all Drivers are uptodate because of security reasons our IT Department takes updates very seriuosly.

And for Windwos we have Windows 10 Enterprise Version 20H2. I think there is no plan to update to Windows 11 in shortly.

Any suggestions if some of my answers have something to do with the problem?

Kindest regards,

Dorella

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Tue, 27 Dec 2022 09:30:13 GMT

Hello Alfred,

I thank You so much for all the effort and time you took to help me out....but unfortunally the last formula also didnßt wor for me.

The same formula worked good for the birthday date 01/11/1982, 1987 and 1958:

But for the birthday date 01/11/1957 it had a day difference in the 20th and 40th anniversary:

I think a difference like this, using both of us the same formula, might have to do with the settings of windows, the date and the separtion...comma or point or something like this...I cannot figure out why we get such different results... It is very stressing... 😪

Anyway, thank you from the deepest of my heart for all your help!

I will do the integer + text conversion and simply write a hint that if the birthday date is on a 29th of februar they should contact the Admni (me 😂). So i can enter the values by hand. I read a very interesting thing: the chances of being born on a 29th of February is about one in 1,461. In fact in my databese of 650 employees I have 0 entries for a Leap Day birthday...

Thank You again and I wish you a nice start in the New Year!

Regards

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

MichelleWilliamsERM — Tue, 27 Dec 2022 21:30:06 GMT

Since Alfred's formulas work, I have a feeling it's your old Survey123. And depending on which version you have in the field, this could be more of an issue.

For example, the software is misaligned if you build in Survey123 Connect 3.13.249 and your end user uses a more current Survey123 field app.

I wonder if the Survey123 web app might be a better fit for your situation because of your IT approval process. I believe Esri is in charge of updating the Web Version, but I'm not 100% sure if that's true.

Re: Calculate Birthdays Anniversary / Jubilee from birthday date.

DoZ — Thu, 29 Dec 2022 13:04:07 GMT

Hello Michelle,

thanks for your concern. Actually i thought it could be a great idea to try theSurvey123 Web Design to try and solve it that way, but unfrotunally it also didn´t work.

I created a birthday date field, and the 18th and the 20th birthday date field, just to try out.

The only way to calculate a date in the future, is to add a certain amount of day. So for the 18th birthday I multiplied 365.25 days18 times (6574.25), and for the 20th birthday I used 7305 days (365.25*12):

On Preview mode you can see I made some attempts on selecting 11th of November 1979, and unfortunally the problem of 1day difference still persist:

It is really unbelivable...I am still wondering if it is me who is doing something wrong...🙄

Thanks again for your help!

Kindest regards😉