Skip to main content
Topics#Sales And MarketingChristian Neuschwanger's Question
Christian Neuschwanger asked in #Sales And Marketing
Apr 21, 2021, 7:18 PM

Formula: Comparing NOW() to a specific day and time each week

I keep beating my head against a wall, and I'm sure the solution is super simple. I need help with a formula/solution that will compare NOW() to every Tuesday at 4PM. I may not be thinking about this correctly either.

 

Use Case: Every time that a case is created I need to auto populate a date field, Sales Period. If the record is created before Tuesday at 4PM, I need populate the Sales Period with the date of the previous Sunday. If the record is created after Tuesday at 4PM, I need to populate the Sales Period date with the date of the following Sunday. I have the formulas that I when manually populating the Sales Period date with either the previous Sunday or following Sunday, but I can't figure out the how to determine it based on Tuesday's at 4PM.

 

Formula to get previous Sunday:

TODAY() - MOD(TODAY()- DATE(1900,1,7),7)

 

Formula to get next Sunday:

TODAY() + 
CASE( MOD(TODAY() - DATE( 1900, 1, 7 ), 7 ), 
0, 7, 
1, 6, 
2, 5, 
3, 4, 
4, 3, 
5, 2, 
6, 1, 
0)

 

 
8 answers
  1. Eric Praud (Activ8 Solar Energies) Forum Ambassador
    Apr 21, 2021, 9:43 PM

    I think it's time for me to go to bed...

    DATEVALUE(CreatedDate)+
    IF(OR(WEEKDAY(DATEVALUE(CreatedDate))<=2,
    AND(WEEKDAY(DATEVALUE(CreatedDate))=3, (HOUR(TIMEVALUE(CreatedDate))+4)*60+MINUTE(TIMEVALUE(CreatedDate))<=16*60)),
    CASE(WEEKDAY(DATEVALUE(CreatedDate)),1,0,2,-1,-2),
    CASE(WEEKDAY(DATEVALUE(CreatedDate)),3,5,4,4,5,3,6,2,1))

     

     
Write an answer...
0/9000