Help

Re: Referencing specific date and time within formula

Solved
Jump to Solution
942 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Dara_Molotsky
5 - Automation Enthusiast
5 - Automation Enthusiast

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")
1 Solution

Accepted Solutions
Dara_Molotsky
5 - Automation Enthusiast
5 - Automation Enthusiast

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"
)

 

See Solution in Thread

6 Replies 6
Manuel_Tanco
5 - Automation Enthusiast
5 - Automation Enthusiast

Did the data you import create new records?

Sho
11 - Venus
11 - Venus

There seems to be a mistake in formula

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

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?

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

Dara_Molotsky
5 - Automation Enthusiast
5 - Automation Enthusiast

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!