I have a field for people names and a field for their birthdays. I like to have a field that calculates the remaining days for their birthdays so that I can sort the upcoming birthdays in an ascending or descending order.

Thanks.

I'm a bit rusty but try this:

Let ( [

d = Get ( CurrentDate ) ;

thisDOB = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( d ) ) ;

nextDOB = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( d ) + 1 ) ;

days = Case ( thisDOB < d ; nextDOB ; thisDOB ) - d

] ;

If (

thisDOB = d ; "Happy Birthday!!!" ;

days & " day" & Case ( days > 1 ; "s" ) & " until your birthday."

)

)

Result of this calculation is text and check 'do not store calculation results...' in the Storage Options so it updates.

UPDATED: improved calc per suggestion below

Yikes, you changed the requirements! If you only want the number of days then change the calc to result of number:

Let ( [

d = Get ( CurrentDate ) ;

thisDOB = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( d ) ) ;

nextDOB = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( d ) + 1 )

] ;

Case ( thisDOB < d ; nextDOB ; thisDOB ) - d

)

UPDATED: improved calc per suggestion below

A small nitpick:

thisDOB - d < 0

 

can be written as:

 

thisDOB < d

I’m not sure I agree and the calcs show different results. Can we walk through the logic?

result of ThisDOB - d might be -97. If so:

-97 < 0 is Boolean test which would return true.

-97 is number result which would return true but so would 369.

Take date of 8/31/xxxx. I suppose I could have used Sign() or reversed the Case() but ... :^)

Oooops. I misread < for -. I now see what you mean. Of course! DOH! I AM rusty!

UPDATE: I am going to correct both calcs in case someone see the calc and grabs it without reading the rest of the thread.

The logic is that a - b returns a negative result if (and only if) b is larger than a.

Another way to look at it is in terms of an equation: you can add the same value to both sides. Start with:

a - b < 0

and add b to both sides.

Thanks. The calculation works fine; however, it does not update the results with current date changes.

check 'do not store calculation results...' in the Storage Options so it updates.

Done. Many thanks