Help

Setting up specific date ranges

Topic Labels: Base design Formulas
128 0
cancel
Showing results for 
Search instead for 
Did you mean: 
DennisyuTSS1
5 - Automation Enthusiast
5 - Automation Enthusiast
  • I have a table that records all of my business leads.
  • I have another table that functions as a report, to provide a summary of all those leads, how much they cost, when they were generated, so on and so forth.
  • These tables are linked
  • Created Date column & Created Month column is automated to fill in this linking field when a record is created in the business leads table, to populate the report

Here's the problem, the business looks at weekly data from Fridays to Thursdays. How can I automate the data so that we can look at the report in the week range that is specific to our business?

Currently, what I can think of is to create a formula field, {Week Range}
The formula would look something like this:

 

 

DATETIME_FORMAT(
   IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Monday', DATEADD(TODAY(), -3, 'days'),
   IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Tuesday', DATEADD(TODAY(), -4, 'days'),
       IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Wednesday', DATEADD(TODAY(), -5, 'days'),
          IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Thursday', DATEADD(TODAY(), -6, 'days'), 
            IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Friday', TODAY(),
               IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Saturday', DATEADD(TODAY(), -1, 'days'), DATEADD(TODAY(), -2, 'days')
               )
            )
         )
      )
   )
),
  'DD MMM YYYY'
)
& ' - ' &
DATETIME_FORMAT(
   IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Monday', DATEADD(TODAY(), 3, 'days'),
   IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Tuesday', DATEADD(TODAY(), 2, 'days'),
       IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Wednesday', DATEADD(TODAY(), 1, 'days'),
          IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Thursday', TODAY(), 
            IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Friday', DATEADD(TODAY(), 6, 'days'),
               IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Saturday', DATEADD(TODAY(), 5, 'days'),
               DATEADD(TODAY(), 4, 'days')
               )
            )
         )
      )
   )
   ),
  'DD MMM YYYY'
)

 


Would there be a more efficient way to do this?
Also, when I replace TODAY() with Created Time, the formula is invalid, anybody knows why?

 

0 Replies 0