Please Help with Conditional Formula to Identify 34 Months Prior to Today's Date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Good Afternoon All.
I am in need of some assistance writing a conditional formula that will return dates 34 months prior to today's date with the date in the column and anything else with a dummy date of 1900-01-01.
Example:
Source data has dates ranging from 09/23/2019 - 08/21/2023.
34 months prior to today would be 02/14/2021.
Every date from 02/14/2021 to current would need to show the date.
Everything prior to 02/14/2021 would need to show 1900-01-01.
Any assistance would be greatly appreciated as I've tried several iterations with unsuccessful results (see below).
Thank you in advance.
if ToNumber(DateTimeMonth(DateTimeToday()))-tonumber(DateTimeMonth([Provider Payment Issue Date]))<=34 then [Provider Payment Issue Date] else "1900-01-01" endif
IIF([Provider Payment Issue Date] <= left(todate(datetimeadd(datetimetoday(),-1,'Month')),7)+'-34', '[Provider Payment Issue Date]', '1900-01-01')
if [Provider Payment Issue Date]<=DatetimeAdd(ToDate(DatetimeNow()),-34,'months') then [Provider Payment Issue Date] else "1900-01-01" endif
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@NagibP here is one way to do this. I would recommend breaking the elements of the formula into their own fields so it is easier to handle in piecemeal. Also, instead of the ToNumber(DateTimeMonth) conversion piece you are doing, I would recommend utilizing the DateTimeAdd function to find the testing date first, then using it as a field in a secondary formula.
Formula logic
Final output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much @rzdodson! You're a lifesaver!
![](/skins/images/A29875142F332EEF75F19ED75711F41B/responsive_peak/images/icon_anonymous_message.png)