Can someone explain the meaning of this formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-2106343%22%20slang%3D%22en-US%22%3ECan%20someone%20explain%20the%20meaning%20of%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3E%3DIF(%24B%2411%26gt%3BDATE(103%2C1%2C1)%2CIF(%24B%2411%3CDATE%3E%3C%2FDATE%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2106343%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106403%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20explain%20the%20meaning%20of%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F946235%22%20target%3D%22_blank%22%3E%40sabriakter%3C%2FA%3E%26nbsp%3BThis%20is%20a%20better%20way%20of%20reviewing%20the%20formula%20and%20the%20Nested%20If%20statement%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%24B%2411%26gt%3BDATE(103%2C1%2C1)%2C%0AIF(%24B%2411%3CDATE%3E%3C%2FDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20line%20is%20checking%20if%20cell%20B11%20is%20after%201%2F1%2F2003%3C%2FP%3E%3CP%3EIf%20TRUE%2C%20it%20checks%20if%20B11%20is%20before%2012%2F1%2F2003%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20if%20TRUE%2C%20it%20will%20return%20the%20date%20in%20B11%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20if%20FALSE%2C%20it%20will%20return%20BLANK%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bthe%20last%20line%20is%20the%20FALSE%20statement%20if%20B11%20is%20after%201%2F1%2F2003%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BThe%20IF%20statement%20reviews%20if%20B11%20is%20before%201%2F1%2F2003%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bif%20TRUE%2C%20it%20returns%201%2F1%2F2003%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bif%20FALSE%2C%20it%20returns%20BLANK%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106609%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20explain%20the%20meaning%20of%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3Ehow%20did%20you%20translate%20the%20(103%2C1%2C1)%20to%201%2C1%2C2003%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

=IF($B$11>DATE(103,1,1),IF($B$11<DATE(103,12,31),MAX(DATE(103,1,1),$B$11),""),IF($B$11<DATE(103,1,1),DATE(103,1,1),""))

3 Replies

@sabriakter This is a better way of reviewing the formula and the Nested If statement:

 

 

=IF($B$11>DATE(103,1,1),
IF($B$11<DATE(103,12,31),MAX(DATE(103,1,1),$B$11),""),
IF($B$11<DATE(103,1,1),DATE(103,1,1),""))

 

 

The first line is checking if cell B11 is after 1/1/2003

If TRUE, it checks if B11 is before 12/1/2003

    if TRUE, it will return the date in B11

    if FALSE, it will return BLANK

 

           the last line is the FALSE statement if B11 is after 1/1/2003:

           The IF statement reviews if B11 is before 1/1/2003,

           if TRUE, it returns 1/1/2003

           if FALSE, it returns BLANK

 

@adversihow did you translate the (103,1,1) to 1,1,2003? 

Excel's dates are integers that starting from a base date, which is typically 1/1/1900, but there is an option to use 1/1/1904. So, 103 years from the presumed base year of 1900 would be 2003 (unless you are using 1904 base year, you could enter =Date(103,1,1) in cell and see what it returns).