Details about this thread
  • State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 294 subscribers
  • Views 1643 views
  • Users 0 members are here
Related content from around SugarClub

Day/TimeStamp within Formula

Courtney Eaton

I have several fields that need to be calculated. One is simple enough "Current Count" - "Old Count" gives me the "Difference". Which in my field this is the formula for "Difference" = subtract($new_bag_count_c,$old_bag_count_c). 

But I need a value to "freeze" every Wednesday at 2 pm EST. So I made a field called "Bag Count Weekly". 

Here is the formula: ifElse(equal(dayofweek(today()),4),$new_bag_count_c,$old_bag_count_c) 

I can't figure out how to add the TimeStamp into the formula, not even sure if I have the rest of it correct or not either. Appreciate the assistance. 

Parents
  • 0

    Hi Courtney Eaton,

    If you need a scheduled event to occur at a specific day and time, calculated fields might not be the right solution. A custom scheduler might be more appropriate. I say this because it is important to keep in mind that nothing automatically updates calculated fields between record save events.

    You can use subStr(), but keep in mind the value subStr(toString($freeze_c),11,2) is going to be based on the active user's date and time format settings, not the database value.

    To capture the hour of now(), you could use:

    subStr(toString(now()),17,2)

    To capture the hour of a dateTime type field, you could use:

    subStr(toString($freeze_c),11,2)

    To experiment with where characters are in an output, you can concatenate the full value and the substring like so:

    concat(toString(now()),", ",subStr(toString(now()),17,2))

    OR

    concat(toString($freeze_c),", ",subStr(toString($freeze_c),11,2))

    I hope this helps!

Reply
  • 0

    Hi Courtney Eaton,

    If you need a scheduled event to occur at a specific day and time, calculated fields might not be the right solution. A custom scheduler might be more appropriate. I say this because it is important to keep in mind that nothing automatically updates calculated fields between record save events.

    You can use subStr(), but keep in mind the value subStr(toString($freeze_c),11,2) is going to be based on the active user's date and time format settings, not the database value.

    To capture the hour of now(), you could use:

    subStr(toString(now()),17,2)

    To capture the hour of a dateTime type field, you could use:

    subStr(toString($freeze_c),11,2)

    To experiment with where characters are in an output, you can concatenate the full value and the substring like so:

    concat(toString(now()),", ",subStr(toString(now()),17,2))

    OR

    concat(toString($freeze_c),", ",subStr(toString($freeze_c),11,2))

    I hope this helps!

Children
No Data