Small Function

[JenniL0211]

Hi Excel pros! 

I am trying to update a spreadsheet for the new year by coping one that was used last year (2023) In the results sheet however, the result for the SMALL formula is coming up #NUM!  and not calculating the way it was.   I am trying to use a master sheet to find the smallest date between 2 cells, both being on separate sheets as well.  So I have a total of 3 sheets.  One for each clerk, and a master for totals.

So if I wanted the Small formula to be on my Master sheet in Cell D2, to generate the smallest date between employee1 D2 (sheet 2) and employee 2 D2 (Sheet 3)  what would be the formula I would need to do this? 

[#2 · Accepted solution]

@JenniL0211 
 you can use the MIN function to find the smallest date between two cells. If you want to find the smallest date between cell D2 on two different sheets (let’s say “Sheet2” and “Sheet3”), you can use the following formula in cell D2 on your master sheet: 

=MIN(Sheet2!D2, Sheet3!D2)

The formula above will return the earliest date between the two cells. If one or both of the cells are empty or do not contain a date, the formula will return #NUM!. To avoid this, you can modify the formula to ignore non-date values:

=MIN(IF(ISNUMBER(Sheet2!D2), Sheet2!D2, ""), IF(ISNUMBER(Sheet3!D2), Sheet3!D2, ""))

[#3]

Hi! Thank you so much for the explanation! The formulas worked perfectly and the explanation for the #NUM as much needed to explain to a supervisor LOL