Forum Discussion

sabriakter's avatar
sabriakter
Copper Contributor
Jan 28, 2021

Can someone explain the meaning of this formula?

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

excel

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jan 28, 2021
    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).
  • adversi's avatar
    adversi
    Iron Contributor
    Jan 28, 2021

    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

     

Resources