Rank field of dates

[Jesper_Holmstro]

Hey Guys

I have a dynamic datefield (updates thru automations) that begins from todays date and records 10 days ahead. Is there a way to write a formulafield that ranks the date with numbers 1-10, i.e so todays date is 1 and tomorrow 2 and so on? I could use that info for navigation in an app.

Thank you in advance.

Jesper

[Brian_Swanson]

Something like this would work.  This is based on an assumption that there is a possibility that you might have a date show that is before or after your 10 day window.  In that case it returns the "OUT OF RANGE" string. 

IF(DATETIME_DIFF({dyanmic date}, NOW(), 'days')<0, 

"OUT OF RANGE",

IF((DATETIME_DIFF({dyanmic date}, NOW(), 'days')<=9),

(DATETIME_DIFF({dyanmic date}, NOW(), 'days')+1), 

"OUT OF RANGE"))

If it is impossible to be out of the range then the formula is simplier:

DATETIME_DIFF({sdyanmic date}, NOW(), 'days')+1

Hope that helps

[Jesper_Holmstro]

Thank you @Brian_Swanson , but I don't want to count in weekends. I have a calendar mon-fri for 2 weeks that the date is updated every midnight by automation. So if today is Wednesday th 3rd may would be number 1, yesterday should be number 10, tomorrow 2 and so on. The weekends is excluded. With the date time_diff it counts the days. I just want a rank 1-10, with the date farest away is 10.

[Brian_Swanson · Accepted solution]

@Jesper_Holmstro , I'm not certain if I follow completely.  In your original post it appears to me that the formula is always forward looking since you refer to looking 10 days ahead.  In your followup you state that it is possible for yesterday to be 10 which means that the formula needs to look forward and back to a certain degree.  

If the goal is to just eliminate the weekends I would think that a formula like this could solve those issues:

WORKDAY_DIFF(NOW(), {Dynamic Date})

I personally have hit a few walls using NOW() within formulas generally a result of some timezone or other issue.  My prefered workaround is to create a formula field with NOW() in it as a Current Date so that I can more easily adjust time zone problems.  In this case I get a table that looks something like this:

The formula for rank then ends up being:

WORKDAY_DIFF({CURRENT DATE}, {Dynamic Date})

Not certain if this helps, but thought it might get you closer to what you are looking for.  

[Jesper_Holmstro]

Thank you @Brian_Swanson , that helped a lot!