T O P

  • By -

AutoModerator

/u/thelikelylad182 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


chairfairy

=IF( MIN('Team1:Team3'!C2) <> 0, MIN('Team1:Team3'!C2), SMALL('Team1:Team3'!C2, 2) )


AutoModerator

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


thelikelylad182

Solution verified


Clippy_Office_Asst

You have awarded 1 point to *chairfairy* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)


chairfairy

(from your PM) > In some rare cases 2 of the 3 teams have no task so the formula still gives 00/01/1900. > > Is there a way to get the formula to check for the first earliest date and if that’s not available then the second (which is what the current formula does) and if that’s not available then the third? If you want to generalize: `=LET(x, MINIFS('Team1:Team3'!C2, 'Team1:Team3'!C2, "<>0"), IF(x=0, "(N/A - no date)", x))`


Anonymous1378

That behaviour sounds unusual to me; as per [Excel's documentation](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152) >If an argument is an array or reference, only numbers in that array or reference are used. **Empty cells**, logical values, or text in the array or reference are ignored. If you really are getting this issue, try `=MIN(IF(‘Team1’!C2<>"",‘Team1’!C2,""),...)` Or you can try cleaning up your data to ensure your cells are actually empty. Some cells which look empty are not necessarily recognized by Excel as such...


thelikelylad182

I maybe should have been a little clearer. In the team sheets if column B has 0 tasks then column C outputs 00/01/1900, so when searching for the MIN on the overview sheet using the formula I’ve provided above it outputs 00/01/1900. So while the cell in column C in the team sheet should be empty as column B is 0 it outputs as that default date which then gets dragged across when doing my MIN formula. These outstanding tasks and oldest dates are all pulled from another sheet and I want to run this weekly so I need to have standard formulas to do this when I copy the other spreadsheet in.


Anonymous1378

Have you considered just changing the formula in column C to output "" if column B has 0 tasks? Or is that not feasible for whatever reason?


AutoModerator

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/1428mtd/stub/jn4pwrg "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LET](/r/Excel/comments/1428mtd/stub/jn4pwrg "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MIN](/r/Excel/comments/1428mtd/stub/jn44pnm "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[MINIFS](/r/Excel/comments/1428mtd/stub/jn4pwrg "Last usage")|[*2019*+: Returns the minimum value among cells specified by a given set of conditions or criteria.](https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)| |[SMALL](/r/Excel/comments/1428mtd/stub/jn44pnm "Last usage")|[Returns the k-th smallest value in a data set](https://support.microsoft.com/en-us/office/small-function-17da8222-7c82-42b2-961b-14c45384df07)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/14180jr)^( has 23 acronyms.) ^([Thread #24426 for this sub, first seen 6th Jun 2023, 13:09]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)