Calculate Birthdays Anniversary / Jubilee from birthday date.
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
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!!😕
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!
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
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😉
