Help

Rank field of dates

Topic Labels: Formulas
Solved
Jump to Solution
903 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

@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:
AT (SS2).png
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.  
 

See Solution in Thread

4 Replies 4
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

@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:
AT (SS2).png
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.  
 

Thank you @Brian_Swanson , that helped a lot!