- 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?
