Sort by the soonest date

[HBarge]

Hi all,

I have a tricky nut to crack here.

I have a list of birthdays in a database.

The users are a bunch of educators who would like to extract birthday data sorted by the soonest birthdays as an output of an app.

Ideally, the first name would be the birthday boy/girl.

What would be the code in the formula tool? I would love if explained with an example.

I would highly appreciate the help!

I love this community.

regards,

Harshad

[haroon_sa]

Hi @HBarge

Here's the solution to your problem. Hope it helps. 

If you have any queries, let me know.

[HBarge]

@haroon_sa Thank you for the attempt however, the sort is not as per the soonest date.

You are attempting to sort by the month and the date. So, it shows the 1st of Jan the first.

I think the formula tool would involve DateTimeToday() function however I am unsure how.

[neilgallen]

@HBarge Use the formula tool to create a new field. This formula would be:

datetimediff([birthday],datetimenow(),"days") and then sort on the newly created field.  Alternatively you could choose whatever interval (days, weeks, months, etc) you wanted as a unit.

[HBarge]

@neilgallen the formula gives values based on days. Hence inherently, a date of birth bearing the year 2006 would be first and 1997 would be the last even when their dates (day and month) is the same. That is not the desired outcome. the desired outcome would be the regardless of the year, the sort should have the soonest coming birthdays.

[neilgallen]

@HBarge Ah, I see the request now. You'll want to use a Julian Date then.

convert both today's date and the birthday into julian dates using the datetimeformat function with %j, and then calculate the difference between the two, which is just simple subtraction.

In this case to know the soonest upcoming birthday you would want to filter your data down to only those records where the julian date of the birthday is greater than today's date.

[HBarge]

@neilgallen

That looks really promising! however, can you help me with the exact formula code?

I would appreciate if you posted a solution.

Thanks!

Harshad

[BenMoss]

Here's how I would resolve the issue.

1. Get the date time today

datetimetoday()

2. Use this to calculate if an individuals birthday has passed this year using a conditional statement.

tostring(DateTimeYear(datetimetoday()))+"-"+right([Date of birth],5)

< datetimetoday()

3. if that condition is met, I.e. their birthday has passed, return their birthday with next years date.

4. If the condition is not met, their birthday must not have yet passed, so return their birthday with this years date.

//nextbirthday

if

tostring(DateTimeYear(datetimetoday()))+"-"+right([Date of birth],5)

< datetimetoday()

then 

tostring(DateTimeYear(datetimetoday())+1)+"-"+right([Date of birth],5)

else

tostring(DateTimeYear(datetimetoday()))+"-"+right([Date of birth],5)

endif

Once you know when their next birthday is you can sort on this field. This date value I guess could be useful in itself too.

I should say, this doesn't work for leap years so you may have to deal with a logic that negates that. 

Ben

[Kaviyarasan_P]

Hi Harshad,

Workflow is attached for your reference.

Thanks

KaviyarasanP

[vishwa_0308 · Accepted solution]

Hi @HBarge,

here i have an work around for the sooner date of birthdays:

1. First replace Year in your Date of Birth List to 2018 using Formula tool :

  Replace([Date of birth], Left([Date of birth],4), DateTimeFormat(DateTimeToday(),"%Y"))

2. Then calculate the difference between current date and Date of birthdays dates in integer variable. It gives no.days for birthdays to come from current date.

 DateTimeDiff([Date of birth],DateTimeToday(),"day")

3. Now filter the birthdays which are gone i.e, negative values which comes from the 2nd step and sort the values to get the sooner date first.

Thanks,

Vishwa