Skip to main content
Solved

Referencing specific date and time within formula

  • July 14, 2023
  • 6 replies
  • 146 views
D
Forum|alt.badge.img+5

I need to be able to write a formula that checks if the last modified date is after a specific date and time. Basically, I imported a large batch of data and I want to track whenever a change is made after the specific date and time of import. I'm having trouble referencing the specific date and time of import within my IF(IS_AFTER) formula. Here is what I have so far - but I know AT isn't reading the date and time I referenced.

 

IF(IS_AFTER({Updated Post Initial Import}), '2023-07-14 120000', "Updated Post Initial Import")

Best answer by Dara_Molotsky

Thanks! This should work - but, it's showing every single record "updated post initial import" even when most are before 12:00:00 (11:58:07, etc). Is it due to timezones that this isn't reading correctly?


Yup - just figured it out, that was the issue.  Had to update formula to be in the same timezone as the import.

Updated to:

IF( IS_AFTER( {Updated Post Initial Import}, '2023-07-14 160000' ), "Updated Post Initial Import" )

 

    CJSmith

    6 replies

    Manuel_Tanco
    Forum|alt.badge.img+8
    • Manuel_Tanco
    • Participating Frequently
    • July 15, 2023

    Did the data you import create new records?

    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • July 15, 2023

    There seems to be a mistake in formula

    IF( IS_AFTER( {Updated Post Initial Import}, '2023-07-14 120000' ), "Updated Post Initial Import" )
    D
    Forum|alt.badge.img+5
    • Dara_Molotsky
    • Author
    • New Participant
    • July 17, 2023

    There seems to be a mistake in formula

    IF( IS_AFTER( {Updated Post Initial Import}, '2023-07-14 120000' ), "Updated Post Initial Import" )

    Thanks! This should work - but, it's showing every single record "updated post initial import" even when most are before 12:00:00 (11:58:07, etc). Is it due to timezones that this isn't reading correctly?

    D
    Forum|alt.badge.img+5
    • Dara_Molotsky
    • Author
    • New Participant
    • July 17, 2023

    Did the data you import create new records?


    Yes - every record is new. Imported the whole data table.

    D
    Forum|alt.badge.img+5
    • Dara_Molotsky
    • Author
    • New Participant
    • Answer
    • July 17, 2023

    Thanks! This should work - but, it's showing every single record "updated post initial import" even when most are before 12:00:00 (11:58:07, etc). Is it due to timezones that this isn't reading correctly?


    Yup - just figured it out, that was the issue.  Had to update formula to be in the same timezone as the import.

    Updated to:

    IF( IS_AFTER( {Updated Post Initial Import}, '2023-07-14 160000' ), "Updated Post Initial Import" )

     

    S
    CJSmith
    D
    Forum|alt.badge.img+3
    • devynoshea
    • Participating Frequently
    • December 13, 2023

    Yup - just figured it out, that was the issue.  Had to update formula to be in the same timezone as the import.

    Updated to:

    IF( IS_AFTER( {Updated Post Initial Import}, '2023-07-14 160000' ), "Updated Post Initial Import" )

     


    Is there a way to use this same formula and only base this just off the time and not the date? For Example I am trying to create the logic: If an order is submitted before 2pm on any day, then that day counts toward the orders turn around time. If an order is submitted after 2pm then that day its submitted does not count toward a turnaround time) 

    I have the workday diff formula but its calculating both the date the order is submitted as well as the delivery date (12/12/2023 11:26am order submitted for a 12/15/23 Delivery Date is showing 4 days turn around when I need it to show 3 ; 12/11/2023 2:28pm order submitted for a  12/15/2023 delivery is reading 5 days turn around) I was trying to nestle the two but I am encountering errors !

    Many thanks for any help or insight! 

    Terms & ConditionsAccessibility statement