Date time calculation with hundreds of days

Question

I have two data sets with date/time (sort of, more on that later) that I need to compare and check to see if the new data and the reference table time are correct or if they're not in sync. If they're not in sync then they need to be passed on for investigation.

Here is the scenario:

New data

Unique IDTimeDifferenceAAAA1860:00:33:18BBBB0:16:00:45CCCC0:00:10:14DDDD0:00:00:45

Reference Table

UniqueIDKnownDifferenceAAAA15BBBB35DDDD20

New Data comes in always as a String and represents days:hours:mins:secs in a dddd:hh:mm:ss

Reference data is a integer represents seconds

I need a way to convert both to a format where they can be compared. Mainly looking for anything >= 2 second difference from the reference table. But with it stretching into days and sometimes kicking out over a thousand days! I need to account for those too.

T_Willins · Accepted Answer

Hi @GoldenDesign04,

If I am understanding your data and explanation correctly, you are comparing the total seconds in TimeDifference with the KnownDifference and looking for any difference >= 2 seconds.  If this is correct, then I wouldn't convert anything into DateTime, but just convert TimeDifference into seconds as per the attached workflow.