pre-1900 dates
I can find no help on how to tell Excel to properly format and calculate dates before 1900. I want to calculate age at death and have birth and death dates in columns. But Excel does not recognize March 30, 1894. It calls is YYYY = 3794. And subtracting death date from birth date returns "#VALUE!" How do I use the DATE(YYYY,MM,DD) function or format March 30, 1894 so Excel will understand and I can have it calculate the age at death correctly? TIA. Also I've seen #1 shown in Excel Help in different posts as BOTH Dec 31, 1899 AND as Jan 1, 1900. Which is it?
Excel Table Appears to Automatically Expand but drop down list doesn't update
I used Excel 2013. I created a drop-down list that is based on an Excel Table via the Data Validation button on the Data ribbon. If I add or delete a row from the middle of the table, my associated drop-downs are updated automatically. However, if I insert a row of data at the very top or bottom of the list (range), even though the table appears to have expanded, the drop-down list does not update automatically. The Auto Correct options "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns" are checked. None of the sheets on my workbook are protected.
Formula or function for IF statement based on cell color
I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. As shown in the picture, if the colors of the cells in column B are the same as those in Column G across the row, I want to subtract the values in columns F and K in the same row to return the absolute value of the subtraction in column L. If the colors of the cells are different, I want to add the values in columns F and K and return the value in column L. I will have multiple tables of varying numbers of rows where I need to perform this operation. Any help is greatly appreciated.
