This is great! I've puzzled over this one a few times to no avail. This will improve data integrity and save some occasional offline file manipulation.
In case it helps anyone else, my particular source column also contained some blanks, which IsNumeric() decided was a number and converted to a date of 12/30/1899. By replacing IsNumeric($_) with $_>40000, it retained the blanks.
Original Message:
Sent: 09-06-2022 10:11
From: Jeremy Wilson
Subject: Convert Excel date code to Date
I've recently come across source files that sometimes use valid dates and and excel date codes other times. Here's a formula to dynamically convert to a date using as a field override:
MyDate =if(IsNumeric($_),DateFormat((value($_)-25569)*24*60*60,"M/d/yyyy"),$_)
This converts an excel date code like 43062 to a date string like 11/23/2017.
#CostingStandard(CT-Foundation)