If someone could help me diagnosis why the nested IF() formula below isn’t returning the expected result, I’d be grateful.
I’ve got a date field (LATEST_PICKUP_DATE) wherein ‘null’ dates default to a date of “1/1/0001 12:00:00,” and that date has a Datameer numerical value of -62135575764000 when used in the INT() function in the calculated field “TEST.”) When this is the case, I want to look at another date field (CREATE_DATE) and add either 6 hours or 24 hours to create a faux LATEST_PICKUP_DATE value. I want to add 6 hours when the SHIPPER is “9670” AND the DEST_FAC_NBR is “9598” AND TEST is -62135575764000, and in ALL other cases when TEST is -62135575764000, I want to add 24 hours. The formula works insofar as it computes as a valid formula, but it allows several values of “1/1/0001 12:00:00” to get through without using the CREATE_DATE and adding the appropriate number of hours.
IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER==9670;#Sheet2!DEST_FAC_NBR==9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+6h;IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER!=9670;#Sheet2!DEST_FAC_NBR!=9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+24h;ASDATE(CONCAT(FORMATDATE(#Sheet2!LATEST_PICKUP_DATE;"MMM dd, Y");" ";#Sheet2!LATEST_PICKUP_TIME);"MMM dd, Y HH:mm:ss")))
In English, what I thought I was saying with the formula above:
IF “TEST” equals “-62135575764000” AND “SHIPPER” equals 9670 AND DEST_FAC_NBR equals 9598, then add 6 hours to the CREATE_DATE, IF “TEST” equals “-62135575764000” AND “SHIPPER” does NOT equal 9670 AND DEST_FAC_NBR does NOT equal 9598, then add 24 hours, else concatenate LATEST_PICKUP_DATE and LATEST_PICKUP_TIME
I hope that's clear enough and look forward to any insight anyone can offer. Thank you!
Please sign in to leave a comment.