Skip to main content

Formula with Date not Displaying Correctly

  • March 19, 2018
  • 2 replies
  • 56 views
A
Forum|alt.badge.img+19

I have 3 Date fields: Created Date (Field Type “Created Date”), Override Date (Field Type “Date”), and Display Date (Field Type “Formula”). The formula in the Display Date column reads the Override Date column, and displays it if it is not blank. If it is blank, it displays the Created Date. Here is the formula:

IF({Override Date}!=BLANK(),{Override Date},{Created Date})

Now, I have two strange things going on here.

  1. If the Override Date is blank, it is all of a sudden not pulling the Created Date instead (had worked just fine until a couple of days ago).

  2. The date being pulled from “Override Date” is off by 5 hours. See below screen grab:

I tried adding a line to the formula to deduct 5 hours from the time, which worked… except for the last record with an Override Date (which deducts 6 hours for some reason??), and is throwing an error on any record with a blank Override Date. See below screen grab:

Any help would be greatly appreciated. I am super confused as to why this is happening! It should be a pretty straight forward formula.

    This topic has been closed for replies.

    2 replies

    A
    Forum|alt.badge.img+19
    • AlliAlosa
    • Author
    • Inspiring
    • March 19, 2018

    To elaborate, I have fixed half of this problem by switching my IF statement around, and by using the CREATED_TIME() function rather than reading the Created Date column. HOWEVER, the time is still off by 4 hours using the CREATED_TIME() function. Here is the new formula:

    IF({Override Date}=BLANK(),DATEADD(CREATED_TIME(),-4,'hours'),DATEADD({Override Date},-5,'hours'))

    Again, this fixed everything except for the last record with an Override Date (in the above screenshots with a Date of “3/15/2018 9:15am” - it should be 10:15 am but the formula is deducting 6 hours from that random record- even though the formula explicitly states to deduct either 4 or 5 hours. Not to mention I have no idea why I should have to tell it to deduct any time at all. What is happening?

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • March 20, 2018

    To elaborate, I have fixed half of this problem by switching my IF statement around, and by using the CREATED_TIME() function rather than reading the Created Date column. HOWEVER, the time is still off by 4 hours using the CREATED_TIME() function. Here is the new formula:

    IF({Override Date}=BLANK(),DATEADD(CREATED_TIME(),-4,'hours'),DATEADD({Override Date},-5,'hours'))

    Again, this fixed everything except for the last record with an Override Date (in the above screenshots with a Date of “3/15/2018 9:15am” - it should be 10:15 am but the formula is deducting 6 hours from that random record- even though the formula explicitly states to deduct either 4 or 5 hours. Not to mention I have no idea why I should have to tell it to deduct any time at all. What is happening?


    To address your first question, try

    IF(
    {Override Date},
    {Override Date},
    {Created Date}
    )

    Admittedly, there should be no difference between IF({Override Date}… and IF({Override Date}!=BLANK()… but on occasion I’ve had trouble with explicit comparisons to BLANK() or ''.

    Your second problem is most likely caused by the formatting on the field being set to use GMT (or not to use GMT, depending on where you are located). Check the formatting for each date field. and if they all seem correct, try toggling GMT on and then back off (or off and then back on), as there have been a few reports of the GMT toggle becoming ‘sticky’.

      Terms & ConditionsAccessibility statement