Google Sheets - Min Date formula gone wonky

[spurs79]

Hi

I have a formula which on native Excel is fine but now has gone Wonky when I put in on Google Sheets. The aim of the formula is to search a list of orders and pick out the first date that rows customer made an order but its throwing up a random date which makes no sense.

Sheet link here https://docs.google.com/spreadsheets...it?usp=sharing

[janmorris]

access to file is denied. can you give access?

[spurs79]

If you request access I can grant it, I dont know how to make it public with an access request

[janmorris]

when getting the share link, simply change the setting to "anyone with the link can" .. "Edit".. or "View"

alternatively... provide a copy of an excel file, which i can then upload to google drive and work on solution.

[spurs79]

Done, new link attached

[janmorris]

great, thank you!

the problem appears to be that your dates in column I are actually text.

if you will be working with the data in both excel and google sheets, change the formula in I2 to :

=DATE(C2,E2,G2)

and drag it down

OR, if you intend to work in Google Sheets only, then delete all the formulas from column I, and put this formula into I2 :

=ARRAYFORMULA(DATE(C2:C,E2:E,G2:G))

but do NOT drag it down


Next,

change your formula in M2 to be :

=SMALL(FILTER($I$2:$I$1000,$B$2:$B=$B2),1)

and the formula in O2 to be :

=LARGE(FILTER($I$2:$I$1000,$B$2:$B=$B2),1)

[janmorris]

corrections, due to rows with no dates:

if excel and google sheets:

=IF(C2="","",DATE(C2,E2,G2))

and drag down

if google sheets only, in I2 :

=ARRAYFORMULA(IF(C2:C="","",DATE(C2:C,E2:E,G2:G)))

and do NOT drag down

EDIT:

more corrections to accommodate for missing dates

in M2:

=IF(C2="","",SMALL(FILTER($I$2:$I$1000,$B$2:$B=$B2),1))

in O2:

=IF(C2="","",LARGE(FILTER($I$2:$I$1000,$B$2:$B=$B2),1))

[janmorris]

BONUS:

DATEDIF formulas are in the format of:

DATEDIF(startDate,endDate,unit)

so Q2 should be :

=DATEDIF(I2,O2,"d")