Sort by the soonest date

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

Birthday List.xlsx

Gallery

Output

Tips and Tricks

Date Time

Apps

Accepted answers

vishwa_0308

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.

Sooner Birthdays.JPG

Thanks,

Vishwa

All comments

haroon_sa

Hi @HBarge

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

If you have any queries, let me know.

Birthday_Lists.yxmd

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.

Kaviyarasan_P

Hi Harshad,

Workflow is attached for your reference.

Thanks

KaviyarasanP

Sort by the Soonest Date.yxmd

vishwa_0308

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.

Sooner Birthdays.JPG

Thanks,

Vishwa

neilgallen

@HBarge See attached workflow.

closest birthdate image.PNG

Find Closest Birthdate.yxmd

HBarge

@vishwa_0308 That works!

This is what we wanted!

Thank you!

HBarge

@neilgallen The workflow is sampling just 1 of the results.

That would inherently filter out all the rest and the output wont be a list. Which is what the requirement is. the user would get an output of lists of people.

If I remove the sampling tool, the output would be exactly what we want. Thank you for the novel approach.

@Kaviyarasan_P Yes, your approach works too! However, I m sorry, I am not able to accept your solution as accepted too.

@BenMoss Thanks for the approach too.

@haroon_sa Thanks!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335